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

Improve this question
6
  • Try changing your TSQL sproc to CREATE PROCEDURE dbo.test_proc AS BEGIN SELECT 'testing' END. Commented Apr 4, 2011 at 3:10
  • Still got the same error although I changed the stored procedure. Commented Apr 4, 2011 at 3: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? Commented Apr 4, 2011 at 3: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! Commented Apr 4, 2011 at 4:26
  • Did you also try exec dbo.test_proc@ss;? Commented Apr 4, 2011 at 4:51

3 Answers 3

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

2 Comments

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.
@jonearles: That's a really good idea!!! I think I can use this method to dynamically call stored procedure! Thanks!!!
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.