| Downloads |
|---|
| 22268.zip |
You can't use a linked server to execute an Oracle stored procedure from SQL Server. Even OPENQUERY and OPENROWSET can't get around that limitation. But you can update an Oracle table from a linked-server query. And if that Oracle table has an UPDATE trigger associated with it, any update will cause the trigger to fire. Furthermore, the trigger can execute a stored procedure. Consequently, by issuing an UPDATE statement, which is a permissible operation, you can indirectly make a stored procedure execute. The code in Listing A demonstrates this process.
First, the code creates an Oracle table. Next, it initializes the table by inserting a row and issuing a COMMIT. Then, the code creates an AFTER UPDATE trigger on the table. From Query Analyzer, you can then execute the following code to trigger the execution of the stored procedure:
SET UPDATE_THIS_TO_EXEC = 1
