使用开放查询功能从 sql server 执行了一个 oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1652168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
executed an oracle stored procedure from sql server using the open query function
提问by Yves
Can you confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I am having problems executing stored procedures with arguments using the OPENQUERY Function.
您能否确认您是否已使用打开查询功能从 sql server 成功执行了 oracle 存储过程。如果是，如何？因为我在使用 OPENQUERY 函数执行带参数的存储过程时遇到问题。
回答by Yves
Finally tracked down a few solutions – the key to the problem (for us) is that by default RPCs are disabled for linked servers. The parameters for Rpc, Rpc Out, and Use Remote Collation need to be set to true. More info:
最后找到了一些解决方案——问题的关键（对我们来说）是默认情况下，链接服务器的 RPC 是禁用的。Rpc、Rpc Out 和Use Remote Collation 的参数需要设置为true。更多信息：
The solution you use will depend upon the procedure output requirements. The first example returns an output value. The second example no output values are returned (data is collected in a subsequent query).
您使用的解决方案将取决于过程输出要求。第一个示例返回一个输出值。第二个示例不返回任何输出值（在后续查询中收集数据）。
Example 1
示例 1
The procedure T2T_collect_all has two input parameters (start and end dates) and one output parameter (row count).
过程 T2T_collect_all 有两个输入参数（开始和结束日期）和一个输出参数（行数）。
DECLARE @l_i_parameter1 varchar(10)
声明@l_i_parameter1 varchar(10)
DECLARE @l_i_parameter2 varchar(10)
声明@l_i_parameter2 varchar(10)
DECLARE @l_i_parameter3 varchar(10)
声明@l_i_parameter3 varchar(10)
DECLARE @l_i_parameter4 varchar(10)
声明@l_i_parameter4 varchar(10)
DECLARE @l_o_parameter1 integer
声明@l_o_parameter1 整数
SET @l_i_parameter1 = '2009/10/01'
SET @l_i_parameter1 = '2009/10/01'
SET @l_i_parameter2 = 'yyyy/mm/dd'
SET @l_i_parameter2 = 'yyyy/mm/dd'
SET @l_i_parameter3 = '2009/12/31'
SET @l_i_parameter3 = '2009/12/31'
SET @l_i_parameter4 = 'yyyy/mm/dd'
SET @l_i_parameter4 = 'yyyy/mm/dd'
SET @l_o_parameter1 = 0
设置@l_o_parameter1 = 0
EXECUTE ( 'begin T2T_collect_all(to_date(?, ?), to_date(?, ?), ? ); end;',
EXECUTE ('开始 T2T_collect_all(to_date(?, ?), to_date(?, ?), ? ); end;',
@l_i_parameter1,
@l_i_parameter2,
@l_i_parameter3,
@l_i_parameter4,
@l_o_parameter1 OUTPUT
) AT ORA_DB;
) 在 ORA_DB;
More Info: http://blogs.msdn.com/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx
更多信息：http: //blogs.msdn.com/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx
Example 2a
示例 2a
The procedure T2T_collect_allx has only two input parameters (start and end dates).
过程 T2T_collect_allx 只有两个输入参数（开始和结束日期）。
EXECUTE ('begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;') ORA_DB;
EXECUTE ('begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;') ORA_DB;
Example 2b
例 2b
SELECT * FROM OPENQUERY(ORA_DB, 'begin T2T_collect_allx (SYSDATE - 40, SYSDATE ); end;')
SELECT * FROM OPENQUERY(ORA_DB, 'begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;')
回答by Hcabnettek
Im no db guy, but what about adding an output parameter on the Oracle proc. Then populate it in the proc and return it to SQL server. Here is an example click here
我不是 db 人，但是在 Oracle proc 上添加输出参数怎么样。然后将其填充到 proc 中并将其返回到 SQL 服务器。这是一个例子点击这里
hth, ~ck
hth, ~ck
