executing an oracle stored procedure from Sql Server 2008

Question

Im trying to execute an oracle stored procedure from SQL Server 2008 R8 trough DB LINK , the header of the stored procedure is like this :

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

This procedure will should update a table "MYTABLE" and return a result

My T-SQL is :

           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 can see that the table "MYTABLE" is updated , but the problem is that im getting an error :

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

That translate in

Cannot execute the instruction at the distant server 'DB_ORACLE'

NB : The parameters for Rpc, Rpc Out, and Use Remote Collation are enabled

Thanks for HELP

Answer 1

for a simple Oracle procedure call from SQL Server

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

calling 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 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;