Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: