5

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

user_0
  • 3,173
  • 20
  • 33
aminedev
  • 323
  • 3
  • 9
  • 22
  • There is probably some whizz bang modern way, so forgive me for asking this folks, but can this be done using a INSERT INTO remTble@DBlink VALUES ( 'stored proc call') , then a trigger on the remote table EXECUTEs the stored proc call, return the results in another table/UPDATE remTble, work? I'm sure I've seen somewhere before. – TenG Jun 12 '15 at 09:57
  • Thanks for the answer , but the problem i can't edit the stored procedure or add a trigger to that table , because i dont have the control on the oracle database / tables. You have to see this like consuming a webservice so if you are the client you cant edit the webservice. – aminedev Jun 12 '15 at 10:03

2 Answers2

0

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

  • Thanks for the answer , but the solution didn't worked for me , i have an additionnal error message : PLS-00363: expression '' ne peut être utilisée comme cible d'affectation ORA-06550: Ligne 1, colonne 53 : PLS-00363: expression '0' ne peut être utilisée comme cible d'affectation ORA-06550: Ligne 1, colonne 7 : PL/SQL: Statement ignored". Msg 7215, Niveau 17, État 1, Ligne 11 Impossible d'exécuter l'instruction sur le serveur distant 'DB_ORACLE'. – aminedev Jun 19 '15 at 09:36
  • can you translate it? –  Jun 19 '15 at 15:33
  • The OLE DB provider "OraOLEDB.Oracle" "DB_ORACLE" linked server returned the message "ORA-06550: line 1, column 50: PLS-00363: expression " 'can not be used as an assignment target ORA-06550: line 1, column 53: PLS-00363: Expression '0' can not be used as an assignment target ORA-06550: line 1, column 7: PL / SQL: Statement ignored ". Msg 7215, Level 17, State 1, Line 10 Can not execute the instruction on the remote server 'DB_ORACLE'. I think the problem is the OUTPUT parameters – aminedev Jun 22 '15 at 08:10
0

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;
Y.B.
  • 3,526
  • 14
  • 24