Problem solve Get help with specific problems with your technologies, process and projects.

Executing stored procedures in 9i from SQL Server

How do you execute stored procedures in Oracle9i from MS SQL Server 2000? Is it possible?
It is possible, but you have a little bit of work to do. First, you'll probably want to visit SQL Server's documentation. For SQL Server 2000, fire up Books Online and choose the Index tab. In the search box, search for the 'heterogeneous data sources, distributed queries' entry. This will give you all the information, or links to the information, on how to set up SQL Server to access data in a non-SQL server (i.e. Oracle) environment. What this doesn't tell you is how to execute a stored procedure. The easiest way I know how to execute an Oracle stored procedure from SQL Server is to change the stored procedure in Oracle to a function. This is pretty easy to do. Use the same PL/SQL block that defines the stored procedure and create a function that returns TRUE at the very end. You can have the function return other values depending on the success of the stored procedure code. Or, have the function just call the stored procedure and return some value. Once you have it in a function, then you'll just have SQL Server run a distributed query which does the following:

SELECT my_function FROM dual

In running this query, the function will execute the stored procedure's code and the output value will be returned to SQL Server. You may or may not care what this value is. Just performing the above SELECT is enough to execute the code.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.