Executing stored procedues in SQL Server 2000 fromOracle 8i

How do you execute stored procedures in SQL Server 2000 from Oracle 8i? 
  ***Procedure SQL Server:
  
  CREATE PROCEDURE [scott].[GetDept]
          @dno INTEGER,
          @dname VARCHAR(14) OUTPUT
  AS
  SELECT
          @dname=DNAME
  FROM
          GTW_DEPT
  WHERE
          DEPTNO=@dno
  GO
  
  ***Procedure Oracle 8i:
  declare
  v_output varchar(14);
  ret integer;
  begin
  ret := "scott"."GetDept"@GTWLINK( 10 , v_output);
  insert into LOCAL_GTW_DEPT values(10 , v_output);
  end;

    Requires Free Membership to View

    Login

The trick is to wrap the stored procedure in a function. You can create a function which calls the stored procedure. The function might return a value indicating success or failure. Then issue a query that accesses that function, similar to: 

SELECT * FROM proc_wrapped_fn@remotedb FROM dual;
If the function is in SQL Server and you want to access it from Oracle, then you will need to use Heterogeneous Services to access the SQL Server database through a database link, as if it were a remote Oracle database.


This was first published in July 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top