14

I have an Oracle linked server in SQL Server 2008 R2. I need to execute Oracle stored procedures (with output parameter in first, and input parameter in second procedure):

CREATE OR REPLACE PROCEDURE my1.spGetDate(CurrentDate OUT VARCHAR2)
IS
BEGIN
-- set output parameter, no select statements
END;

CREATE OR REPLACE PROCEDURE my1.spDeleteOldRecords(CurrentDate IN VARCHAR2)
IS
BEGIN
-- conditional delete from oracle table, no select statements
END;

I didn't found any complete documentation on this question, only simple examples with parameterless select/nonselect procedures, and want to know, how to call these procedures, procedures with select inside, multiparameter procedures with basic parameter types.

David Levin
  • 6,573
  • 5
  • 48
  • 80

2 Answers2

13

It should work like this:

DECLARE @dateval DATETIME

EXECUTE ('begin my1.spGetDate(?); end;', @dateval OUTPUT) AT ORA_DBLINK_NAME;

EXECUTE ('begin my1.spDeleteOldRecords(?); end;', @dateval) AT ORA_DBLINK_NAME;

If you have several parameters, it could look like this:

EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;
Codo
  • 75,595
  • 17
  • 168
  • 206
  • Thanks, examples work corretly. Can you add examples with select inside (select only, select and output parameter, etc.). – David Levin Dec 12 '12 at 09:28
  • What do you mean by "select"? A way of returning the some variables from the SQL Server stored procedure? Or processing the result set of a SELECT run by the Oralce stored procedure? – Codo Dec 12 '12 at 09:32
  • I mean procedures with result set. – David Levin Dec 12 '12 at 09:45
  • As far as I know, it's not possible. There seem to be workarounds using some .NET code but I can't help you with that. – Codo Dec 12 '12 at 11:33
  • 2
    1. "begin" and "end;" are required as part of the statement. 2. SQL Server linked server properties must have "RPC=True" and RPC Out=True". Holy smokes, searched for hours this morning trying to do this (with INSERT INTO ... RETURNING). Thank you! – leqid Apr 16 '14 at 19:08
  • You can return result sets from an Oracle linked server by using [PIPELINED table functions](http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm), but you'll have to wrap the function in a package with some declarations that specify the fields and data types that are to be returned. It was a pain, but I've done it. You call it from SQL Server like this: `EXECUTE ('SELECT * FROM TABLE(..(?))',@Parameter) AT ;` – Baodad Aug 15 '14 at 22:40
  • @Codo Can I give name of the parameter instead of (**?,?,?,?**). For example, in oracle SP the name of the parameter are -> **fname, lname, regno**, id then it be like: `EXECUTE ('begin my1.spProc(fname,lname,regno,id); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;` I have more than 40 parameters to pass, so better if I specify with param_name. – nirav Apr 03 '19 at 04:42
  • 1
    @nirav: To the best of my knowledge, that's not possible. – Codo Apr 03 '19 at 06:02
1

Use REF CURSOR and declare that cursor as an output variable in oracle. Using Ref Cursor you can retrieve result set from Oracle Stored procedure

Nivin Govindan
  • 11
  • 1