通过 Oracle 的数据库链接运行 SQL Server 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5534176/
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
Run SQL Server Stored Procedure via Database Link from Oracle
提问by Alex Yeung
Referring to How to execute an Oracle stored procedure via a database link, it does not work in my case. I don't know what I am missing.
参考如何通过数据库链接执行 Oracle 存储过程，它在我的情况下不起作用。我不知道我错过了什么。
I have a SQL Server instance and Oracle database in the same computer. And database link is created in the Oracle, called ss.
我在同一台计算机上有一个 SQL Server 实例和 Oracle 数据库。而数据库链接是在 Oracle 中创建的，称为ss.
And there is a stored procedure in SQL Server called dbo.test_proc
SQL Server 中有一个存储过程叫做 dbo.test_proc
create proc dbo.test_proc
as
print 'testing';
Apparently, it does not have parameter and no return value.
显然，它没有参数，也没有返回值。
I tried to call the stored procedure in Oracle via database link. But noneof the following work.
我试图通过数据库链接调用 Oracle 中的存储过程。但是没有下面的工作。
exec test_proc@ss;
exec "test_proc"@ss;
exec "dbo"."test_proc"@ss;
exec "dbo.test_proc"@ss;
exec "dbo..test_proc"@ss;
The error is like
错误就像
PLS-00201: identifier 'test_proc@SS' must be declared
ORA-06550: line 1, column 7:
Could any help me that? I have tried for a long time. Thanks!
有谁能帮我吗？我已经尝试了很长时间。谢谢！
Image is uploaded for querying sys.proceduresto check the existence of the stored procedure in SQL Server and trying to run the stored procedure via database link.
Image
上传图片以查询sys.proceduresSQL Server中是否存在存储过程，并尝试通过数据库链接运行存储过程。
图片
采纳答案by Alex Yeung
After google it for a while, I finally found that I am using dg4odbcwhich is NOTsupporting calling stored procedure in SQL Server.
google了一段时间后，我终于发现我使用的是不支持在SQL Server中调用存储过程的dg4odbc。
http://forums.oracle.com/forums/thread.jspa?threadID=1131396&tstart=0
http://forums.oracle.com/forums/thread.jspa?threadID=1131396&tstart=0
However, my computer is Windows 7, so I am still looking for solution. Thanks!
但是，我的电脑是 Windows 7，所以我仍在寻找解决方案。谢谢！
Update: jonearles gives me a tricky idea but it works. See the comments below.
更新：jonearles 给了我一个棘手的想法，但它有效。请参阅下面的评论。
回答by Matt Hopkins
It is actually possible to call stored procedures or functions via dg4odbc. I tested Database Gateway for MS SQL Server, and it failed to support Sql Server table/scalar-valued functions natively anyway. Both need to rely on DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATEfor this functionality. We needed to retrieve the id of inserted rows:
实际上可以通过 dg4odbc 调用存储过程或函数。我测试了 MS SQL Server 的数据库网关，但无论如何它都无法原生支持 Sql Server 表/标量值函数。两者都需要依赖DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE来实现此功能。我们需要检索插入行的 id：
DECLARE
RESULT NUMBER(8,2);
val INTEGER;
c INTEGER;
nr INTEGER;
BEGIN
RESULT := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@mssqldb('select SCOPE_IDENTITY();');
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@mssqldb;
DBMS_HS_PASSTHROUGH.PARSE@mssqldb(c, 'select @@IDENTITY');
LOOP
nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@mssqldb(c);
EXIT WHEN nr = 0;
DBMS_HS_PASSTHROUGH.GET_VALUE@mssqldb(c, 1, val);
END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@mssqldb(c);
DBMS_OUTPUT.PUT_LINE('retrieved: ' || val);
END;
回答by Manoj
This is how you process multiple columns of a result set coming from SQLServer SP.
这是处理来自 SQLServer SP 的结果集的多列的方式。
DECLARE
l_cursor BINARY_INTEGER;
v_sql VARCHAR2(32767);
v_res_1 NUMBER; -- change to suite your datatype in SQLServer
v_res_2 VARCHAR2(100); -- change to suite your datatype in SQLServer
BEGIN
v_sql := 'EXEC getRecords @parameter1=''somevalue'', @parameter2=''somevalue'';';
l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@sqlserver;
DBMS_HS_PASSTHROUGH.parse@sqlserver(l_cursor,v_sql);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@sqlserver (l_cursor, 1 ,v_res_1);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@sqlserver (l_cursor, 2 ,v_res_2);
WHILE DBMS_HS_PASSTHROUGH.fetch_row@sqlserver(l_cursor) > 0
LOOP
DBMS_HS_PASSTHROUGH.get_value@sqlserver(l_cursor, 1, v_res_1);
DBMS_HS_PASSTHROUGH.get_value@sqlserver(l_cursor, 2, v_res_2);
Dbms_Output.put_line('Result : ' || v_res_1 || ' - ' || v_res_2);
END LOOP;
DBMS_HS_PASSTHROUGH.close_cursor@sqlserver(l_cursor);
END;
I can see that its an year old post, but some one else may stumble upon it. :)
我可以看到这是一个一年前的帖子，但其他人可能会偶然发现它。:)
Still have doubts regarding the performance of this approach. Any insight on this by the experts here would be most appreciated.
仍然对这种方法的性能有疑问。非常感谢这里的专家对此的任何见解。
