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

Solution 1

After google it for a while, I finally found that I am using dg4odbc which is NOT supporting calling stored procedure in SQL Server.

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!

---

Update: jonearles gives me a tricky idea but it works. See the comments below.

Solution 2

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;

Solution 3

This is how you process multiple columns of a result set coming from 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.