How to call an Oracle Function with multi out Parameters though LINKSERVER

Question

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;

Answer 1

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.