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 last published in July 2004

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close