Oracle – Run SQL Server Stored Procedure via Database Link from Oracle

dblinkoraclesql-server-2008

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.

I have a SQL Server instance and Oracle database in the same computer. And database link is created in the Oracle, called ss.

And there is a stored procedure in SQL Server called 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 none of the following work.

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.procedures to check the existence of the stored procedure in SQL Server and trying to run the stored procedure via database link.
Image

Best Answer

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_IMMEDIATE for this functionality. We needed to retrieve the id of inserted rows:

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;
Related Topic