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.

Improve this question

2 Answers 2

Reset to default
13
+50

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks, examples work corretly. Can you add examples with select inside (select only, select and output parameter, etc.).
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?
I mean procedures with result set.
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!
@nirav: To the best of my knowledge, that's not possible.
|
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.