Sql-server – How to call an Oracle Function with multi out Parameters though LINKSERVER

functionslinked-serveroraclesql server

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;

Best Answer

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.

Related Solutions

SQL Server – How to Query a Remote Server Using a Local Function

Seems (based on the name, anyway) like a pretty basic scalar UDF that just massages strings, won't be subject to a lot of changes over time, and doesn't rely on any data. So, I suggest you make a copy of the function on the source server, and fight whatever rule is preventing you from doing so (it shouldn't be hard if you can justify the use of the function, and describe what kind of hack you're trying to use as a workaround to the rule). Note that the function doesn't have to live in the same database, it just needs to be on the same server.

(Even if the function does rely on data, and it's clear in your case it doesn't, the local copy of the function could reference the data in the other server, using 4-part name(s), without referencing any functions.)

DRY is not always worth pursuing.

SQL Server – How to Call a Remote User Defined Function

Your remote query is nested twice, first in OPENQUERY, then in EXEC. The @somevalue variable is declared at the outermost level, it simply does not exist in the remote query's scope, so the reference is invalid.

But you also cannot use a variable there for two reasons:

First of all, as Colin 't Hart has noted, your linked server is Oracle. The SELECT @var = function(...) syntax is SQL Server and invalid in Oracle.
the OPENQUERY rowset function lets you treat the remote side's result only as a rowset – it is not designed to populate variables, whether along the way or as an alternative to the rowset. So, even if @var-like references were allowed in the underlying query (for instance, if the remote server was SQL Server), with OPENQUERY you would still be unable to reference outer ("this" side's) variables at the remote query's scope, nor would the outer scope be able to access variables declared at the inner scope ("that" side) in case you wanted to use them in your remote query.

To conclude at this point, the best way to get a result from an Oracle scalar function in SQL Server is probably to have your remote query return the result as a rowset while invoking the remote query using a rowset function like OPENQUERY. After you get the rowset from OPENQUERY, you can do with it whatever SQL Server allows you to do, and that includes storing its column values into variables. So, your OPENQUERY query could basically go like this:

SELECT
  @somevalue = somecolumn
FROM
  OPENQUERY(
    'test',
    'your Oracle query'
  )
;

Note the position of @somevalue = – it is moved one level higher, where it is now valid syntax and makes sense. The somecolumn would be the name that you would assign to your Oracle query's single column returning the result. This is probably how your Oracle query should look like:

SELECT getvalue(argument) AS somecolumn FROM DUAL

Now, if you did not need to parametrise argument, the complete Transact-SQL query could look like this:

DECLARE @somevalue nvarchar(16);

SELECT
  @somevalue = myresult
FROM
  OPENQUERY(
    test, 
    'SELECT getvalue(545454545) AS myresult FROM DUAL'
  )
;

But as you want to be able to parametrise the argument, you need to make the SELECT FROM OPENQUERY query dynamic: build it as a string:

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
  …
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

and then execute the string:

EXEC (@sql);

All that you are already doing. However, when you use the EXEC (...) syntax, you cannot directly receive the result into a variable¹, you can only get it as a result set – yes, same limitation as with OPENQUERY. Unlike OPENQUERY, though, you cannot call EXEC in a FROM clause.

So, at this point you are still facing the problem of putting the result of getvalue into a variable.

Basically, there are two ways to go about it. First is to drop the @somevalue = bit from the dynamic query to let the OPENQUERY's result set pass through to the outermost scope. Although you cannot do ... FROM EXEC (...), you can do INSERT ... EXEC (...). So, insert the result set of EXEC into a temporary dataset (like a table variable):

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

then read the column value from the temporary storage into a scalar variable:

SELECT @somevalue = result FROM @tmpresult;

The other, and, in my opinion, better, option is to use EXEC sp_executesql instead of EXEC () to execute the dynamic query. With the sp_executesql system stored procedure you can have parameters for the underlying query, including output parameters. An output parameter is precisely how you can pass the result into your @somevalue variable that you have declared in your main script.

So, keep the @somevalue = part of the dynamic query but declare it when invoking the query rather than in the query. That way the variable will work not just as a local variable but as a parameter. Including OUTPUT into the parameter declaration will make it an output parameter too. So, this is how you go:

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  …
';

EXEC sp_executesql @sql, '@tmpvalue nvarchar(16) OUTPUT', @somevalue OUTPUT;

You can see in the above code that I slightly changed the name of the variable in the dynamic query. I did that to emphasise the fact that the variable in your dynamic query and the one in your main script are different things. The @tmpvalue is the variable with which you work inside the dynamic query, while @somevalue is your main scope's variable that you will be passing as an actual receiver of the value stored in @tmpvalue. That is the way of exchanging data between variables of a dynamic query and those of the calling script. Note also that you must specify OUTPUT both in the parameter declaration and when specifying the receiver variable. This is the same convention as with output parameters of user-defined stored procedures.

So, to summarise, the final code would look something like this:

DECLARE
  @sql      nvarchar(max),
  @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

EXEC sp_executesql
  @sql,
  N'@tmpvalue nvarchar(16) OUTPUT',
  @somevalue OUTPUT
;

_{¹Actually you can have parameters, including output parameters, with EXEC, but only if you have specified to execute the dynamic query at a remote server:}

_{EXEC (@sql, @var1, @var2, ...) AT linked_server}

_{Usually this is more useful when the remote server is a non-SQL Server one, as for SQL Server instances the EXEC sp_executesql method would likely be preferable.}

Best Answer

Related Solutions

SQL Server – How to Query a Remote Server Using a Local Function

SQL Server – How to Call a Remote User Defined Function

Related Question