Run SQL Server Stored Procedure via Database Link from Oracle

Question

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

Answer 1

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;