I had to to this a while back -> Calling an Oracle stored procedure from a Sql Server stored procedure. The Oracle stored procedure accepted a number of parameters from the Sql Server stored procedure. The Oracle stored procedure would then execute and pass back a result set to the Sql Server calling procedure. A linked server was used in Sql Server to call the Oracle stored procedure. This is just to save anybody the pain in case they need to set this up.

This is a step by step example on how to set up the server where the Oracle database is installed and the server where the Sql Server is installed. It also provides a test Sql Server stored proc calling an Oracle stored proc with parameters, with a result set being passed back to Sql Server.

The following are the steps necessary to call an Oracle Stored Procedure from Sql Server.

The test was done with two separate computers as below.

Pre Set Up:

Machine 1)

Windows XP , with Oracle Standard Server 8.1.7 installed.

Note: Other versions of Oracle may work.

Machine 2)

Windows XP , with Sql Server 2000 installed.

Machine 1) Oracle

1) Make sure the Distributed Transaction Coordinator Service is running.

2) Run the following Below Oracle Packages and Procs into the Oracle Instance.

Note :The Oracle stored procedure that Sql Server calls must be within a package.

IN parameters can be passed into the Oracle Stored Procedure, but any results