通过Oracle的数据库链接运行SQL Server存储过程
参考如何通过数据库链接执行Oracle存储过程，它在我的情况下不起作用。我不知道我错过了什么。
我在同一台计算机上有一个SQL Server实例和Oracle数据库。数据库链接在Oracle中创建，名为
ss
dbo.test_proc
create proc dbo.test_proc
as
print 'testing';
exec test_proc@ss;
exec "test_proc"@ss;
exec "dbo"."test_proc"@ss;
exec "dbo.test_proc"@ss;
exec "dbo..test_proc"@ss;
PLS-00201: identifier 'test_proc@SS' must be declared
ORA-06550: line 1, column 7:
sys.procedures
没有找到相关结果
已邀请:
3 个回复
席酱
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;攫怂绵十
搜洼挂时
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;