使用打开查询功能从sql server执行了oracle存储过程 [英] executed an oracle stored procedure from sql server using the open query function

问题描述

是否可以使用打开查询功能从sql服务器确认是否已成功执行oracle存储过程.如果是，如何?因为我在使用OPENQUERY函数执行带有参数的存储过程时遇到问题.

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.

推荐答案

最后找到了一些解决方案–该问题的关键(对我们而言)是默认情况下，链接服务器的RPC被禁用. Rpc，Rpc Out和Use Remote Collat​​ion的参数需要设置为true.更多信息:

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:

您使用的解决方案将取决于过程输出要求.第一个示例返回一个输出值.第二个示例不返回任何输出值(在后续查询中收集数据).

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

示例1

过程T2T_collect_all具有两个输入参数(开始和结束日期)和一个输出参数(行计数).

The procedure T2T_collect_all has two input parameters (start and end dates) and one output parameter (row count).

DECLARE @ l_i_parameter1 varchar(10)

DECLARE @l_i_parameter1 varchar(10)

DECLARE @ l_i_parameter2 varchar(10)

DECLARE @l_i_parameter2 varchar(10)

DECLARE @ l_i_parameter3 varchar(10)

DECLARE @l_i_parameter3 varchar(10)

DECLARE @ l_i_parameter4 varchar(10)

DECLARE @l_i_parameter4 varchar(10)

DECLARE @ l_o_parameter1整数

DECLARE @l_o_parameter1 integer

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

SET @l_o_parameter1 = 0

EXECUTE('开始T2T_collect_all(to_date(?,?)，to_date(?,?)，?);结束;'，

EXECUTE ( 'begin 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;

) AT ORA_DB;

更多信息:示例2a

过程T2T_collect_allx仅具有两个输入参数(开始和结束日期).

The procedure T2T_collect_allx has only two input parameters (start and end dates).

EXECUTE('从T2T_collect_allx开始(SYSDATE-40，SYSDATE);结束;')ORA_DB;

EXECUTE ('begin T2T_collect_allx (SYSDATE - 40, SYSDATE); end;') ORA_DB;

示例2b

SELECT * FROM OPENQUERY(ORA_DB，'开始T2T_collect_allx(SYSDATE-40，SYSDATE);结束;')

SELECT * FROM OPENQUERY(ORA_DB, 'begin T2T_collect_allx (SYSDATE - 40, SYSDATE ); end;')

这篇关于使用打开查询功能从sql server执行了oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！