***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