Q

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;

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close