1

I need help as I am facing this problem. when I pass 2 output parameter to a function through a linked server I am getting this error. For 1 out parameter its working fine but when I use two output parameters it gives this error:

“Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

Here is the query:

DECLARE @RET INT

EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
, @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
, @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE
, @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
AT linkserver_name;
2

You can try same trick as mentonied at this answer on StackOverflow.

In your case SQL statement string for EXECUTE must look like this:

DECLARE 
  vRetValue number;  
  vErrorCode varchar2(100);
  vErrorDescription varchar2(4000);
  vRetCursor sys_refcursor;
BEGIN 
  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription); 
  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual;
  ? := vRetCursor;
END;

And call to this code like this:

DECLARE @RET_CURSOR CURSOR;

EXECUTE (
  'DECLARE '+
  '  vRetValue number;  '+
  '  vErrorCode varchar2(100); '+
  '  vErrorDescription varchar2(4000); '+
  '  vRetCursor sys_refcursor; '+
  'BEGIN '+
  '  vRetValue := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,vErrorCode, vErrorDescription); '+
  '  open vRetCursor for select vRetValue, vErrorCode, vErrorDescription from dual; '+
  '  ? := vRetCursor; '+
  'END;' ,
  @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH,
  @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, 
  @PASSPORT_NO, @PPT_EXP_DATE,
  @RET_CURSOR OUTPUT
) AT linkserver_name;

Sorry, I can't check the syntax, but hope that principle is clear enough.

Another thing is the code above is a workaround and must be used only if code with multiple output parameters won't work at all.
Most probable source of such behavior is a bugs or version incompatibility of MS OLE DB Provider for Oracle or Oracle client.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.