Running oracle stored procedure from Sql Server 2008

Question

I am trying to execute an oracle stored procedure from SQL Server 2008 R8 through DB LINK, the stored procedure header looks like this:

TEST PROCEDURE (X OUT NOCOPY VARCHAR2, Y NOCOPY NUMBER, Z IN NUMBER)

This procedure should update the table "MYTABLE" and return the result

My T-SQL:

DECLARE @X nvarchar(255) DECLARE @Y INTEGER DECLARE @Z INTEGER SET @X= '' SET @Y = 0 SET @Z = 2 EXEC('begin USER.PKG.TEST(?,?,?); end;',@X OUTPUT, @Y OUTPUT,@Z ) AT DB_ORACLE; 

The stored procedure is executed because I see that the table "MYTABLE" is updated, but the problem is that im is getting an error message:

 Msg 7215, Niveau 17, État 1, Ligne 10 Impossible d'exécuter l'instruction sur le serveur distant 'DB_ORACLE'. 

Translate

 Cannot execute the instruction at the distant server 'DB_ORACLE' 

Note: Rpc, Rpc Out, and Use Remote Collation options are enabled.

thanks for the help

Answer 1

to simply call an Oracle procedure from SQL Server

exec ('begin sproc_name; end;') at linked_server_name

call procedure with variables

 <B>declare @z int<B> <B>set @z = 10 <B> exec ('begin sproc_name(''' + @z + '''); end;') at linked_server_name; 

This works fine for me use

Answer 2

Passing output variables through the result set should work:

 DECLARE @X nvarchar(255) = ''; DECLARE @Y int = 0; DECLARE @Z int = 2; DECLARE @Result As Table (X nvarchar(255), Y int); INSERT INTO @Result (X, Y) EXEC('declare X nvarchar(255) = ?; Y int = ?; Z int = ?; begin USER.PKG.TEST(X, Y, Z); select X, Y from DUAL; end;', @X, @Y, @Z) AT DB_ORACLE; SELECT @X = X, @Y = Y FROM @Result;