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.