How do you execute stored procedures in SQL Server 2000 from Oracle 8i?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
***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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.