I have an oracle function which needs to call a SQL Server Stored Procedure. I created a database link in oracle to the SQL Server. I'm using DBMS_HS_PASSTHROUGH to call the stored procedure and it actually calls it. The stored procedure has some logic in it. There are some select statements in the Stored Procedure and those actually work, but it also contains some update/insert/delete statements and those do not work.
When I commented out all the update/insert/delete, I get a successful response from the SQL Server executing the Stored Procedure back to Oracle. When those updates and inserts are to be performed, I get "ORA-00942: table or view does not exist"
Like I said I'm able to execute stored procedure, also a nested stored procedure gets executed, functions work as well. The problem is when update/insert/delete are to be performed. I checked the permission on the table and all seem to be correct.
For example, if I Deny execution of stored procedure for the DBLINK user, I will get a response Access Denied. But if I Deny Select Statements for the DBLINK for that table, those Select Statements will still get executed. It seems like if once we are in the Stored Procedure, the user executing the select/update/insert/delete is not longer "DBLINK".
any help would be appreciated!
I'm not expert on this, so if you guys know of a better way to call a stored procedure from oracle, please let me know.