使用SQL Server的输出参数调用Oracle存储过程

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):

我在SQL server 2008 R2中有一个Oracle链接服务器。我需要执行Oracle的存储过程(第一个是输出参数，第二个是输入参数):

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.

在这个问题上我没有找到任何完整的文档，只有没有参数选择/非选择过程的简单示例，并且想知道如何调用这些过程，在内部选择的过程，以及具有基本参数类型的多参数过程。

2 个解决方案

#1

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;

#2

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

使用REF游标并声明游标为oracle中的输出变量。使用Ref游标可以从Oracle存储过程中检索结果集

#1