从 SQL Server 存储过程执行 Oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7262158/
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
Execute an Oracle stored procedure from a SQL Server stored procedure
提问by Miles
I have a SQL Server stored proc that needs to send information to an Oracle stored proc.
我有一个 SQL Server 存储过程，需要将信息发送到 Oracle 存储过程。
How do I set this up? Should it be a linked server? And how do I write the code to pass and execute the stored proc?
我该如何设置？它应该是链接服务器吗？以及如何编写代码来传递和执行存储过程？
采纳答案by Miles
Turns out I ended up having to use Microsofts CLR and write my own little C# application in order to handle the variables being pushed between the servers. Here's what I did:
结果我最终不得不使用微软的 CLR 并编写我自己的小 C# 应用程序，以便处理在服务器之间推送的变量。这是我所做的：
- C# Application starts on a scheduled task in windows
- Application executes the SQL Server stored procedure and gets the return values
- Application then executes the Oracle stored procedure with appropriate values
- C# 应用程序在 Windows 中的计划任务上启动
- 应用程序执行 SQL Server 存储过程并获取返回值
- 应用程序然后使用适当的值执行 Oracle 存储过程
回答by hagensoft
You could also use:
您还可以使用：
DECLARE @outputParameter int
EXEC ('BEGIN <procedureName>(?,?); END;', @inputParameter , @outputParameter OUTPUT) at <linkedServerName>
This allows capturing output parameters
这允许捕获输出参数
回答by markmolamhoi
a procedure "TestingProcedure" in Oracle server.
Oracle 服务器中的过程“TestingProcedure”。
a linked Server "LinkedServer" in SQL Server.
SQL Server 中的链接服务器“LinkedServer”。
call TestingProcedure in SQL server example:
在 SQL 服务器示例中调用 TestingProcedure：
EXECUTE ('begin TestingProcedure; end;') AT LinkedServer;
回答by Tom H
This is the syntax that I would probably try from the start:
这是我可能从一开始就尝试的语法：
SELECT * FROM OPENQUERY(<linked server name>, '{CALL <oracle sp>}')
What I've seen (I have not tried this myself) is that you can also use OPENQUERYto call an Oracle function which can then call the stored procedure, or you can turn the stored procedure into a function. Your connection from SQL Server would then look something like this:
我所看到的（我自己没有尝试过）是，您还可以使用OPENQUERY调用 Oracle 函数，然后该函数可以调用存储过程，或者您可以将存储过程转换为函数。您从 SQL Server 的连接将如下所示：
SELECT * FROM OPENQUERY(<linked server name>, 'SELECT <oracle function> FROM dual')
