3

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

Community
  • 1
  • 1
Alex Yeung
  • 2,495
  • 7
  • 31
  • 48
  • Try changing your TSQL sproc to `CREATE PROCEDURE dbo.test_proc AS BEGIN SELECT 'testing' END`. – OMG Ponies Apr 04 '11 at 03:10
  • Still got the same error although I changed the stored procedure. – Alex Yeung Apr 04 '11 at 03:19
  • How did you define your link - can you post the CREATE DATABASE LINK command you used? Can you issue a simple select statement on a table in the SQL Server database from the Oracle database and have it succeed? – DCookie Apr 04 '11 at 03:52
  • Yes. The database link works fine. I could do `select * from INFORMATION_SCHEMA.TABLES@ss`. I just uploaded an image for the result. Thanks! – Alex Yeung Apr 04 '11 at 04:26
  • Did you also try `exec dbo.test_proc@ss;`? – Jon Heller Apr 04 '11 at 04:51
  • Yes. I tried `exec dbo.test_proc@ss;` but it does not work. The error is `PLS-00201: identifier DBO.TEST_PROC@SS must be declared`. – Alex Yeung Apr 04 '11 at 04:56

3 Answers3

3

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;
Matt Hopkins
  • 211
  • 2
  • 2
2

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.

Manoj
  • 41
  • 2
  • 6
2

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.

Alex Yeung
  • 2,495
  • 7
  • 31
  • 48
  • 1
    If there's no way to directly call the stored procedure you can probably call it indirectly through a trigger. Create a table on SQL Server and add a trigger that calls the procedure on insert. Then create an Oracle procedure that inserts one row into that table. – Jon Heller Apr 05 '11 at 04:44
  • @jonearles: That's a really good idea!!! I think I can use this method to dynamically call stored procedure! Thanks!!! – Alex Yeung Apr 06 '11 at 02:45