I am trying to execute an oracle pl/sql stored procedure from within sql server via tsql. I have used linked servers many times to perform selects and updates from sql server to and from oracle. I am looking for tsql syntax to call oracle procedure. Thank You.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
execute oracle procedure from sql server tsql
- Thread starter TWillard
- Start date
Take your rowset returning Oracle query. I don't know the syntax for that, but imagine it's 'EXEC MyProc', then:
If you have to include parameters in your query, the only way to do this is with Dynamic SQL as you can't have complex expressions inside OPENQUERY(). Build the openquery SELECT statement as a string and then use EXEC (@SQL).
[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.
The best part about anything that has cheese is the cheese.[/color]
Code:
SELECT * FROM OPENQUERY(OracleLinkedServer, 'EXEC MyProc') XIf you have to include parameters in your query, the only way to do this is with Dynamic SQL as you can't have complex expressions inside OPENQUERY(). Build the openquery SELECT statement as a string and then use EXEC (@SQL).
[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.
The best part about anything that has cheese is the cheese.[/color]
- Thread starter
- #3
Thank you for the reply ESquared. However, I get the following error message. I can execute this same procedure with success using oracle's sqlplus. This procedure does not have parameters.
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC PR_TEST". The OLE DB provider "MSDAORA" for linked server "LINKSERVERNAME" indicates that either the object has no columns or the current user does not have permissions on that object.
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC PR_TEST". The OLE DB provider "MSDAORA" for linked server "LINKSERVERNAME" indicates that either the object has no columns or the current user does not have permissions on that object.
I would in this case trust the error. I bet you an organic fig that it's exactly as it says.
Does your SP return rows? If not, that is the problem. Otherwise, it's probably a permissions problem.
Try a simpler query: SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM SomeTable WHERE rownum <= 5') X
Does that work? I can't remember the rownum syntax, but basically you should try a simple select of a few rows.
If you validate the linked server by selecting a few rows, then you're entirely in Oracle land (or SQL Server linked server permission/login setup land) for any other problems you get.
Does your SP return rows? If not, that is the problem. Otherwise, it's probably a permissions problem.
Try a simpler query: SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM SomeTable WHERE rownum <= 5') X
Does that work? I can't remember the rownum syntax, but basically you should try a simple select of a few rows.
If you validate the linked server by selecting a few rows, then you're entirely in Oracle land (or SQL Server linked server permission/login setup land) for any other problems you get.
- Thread starter
- #5
My problem was that the query was not returning rows. The procedure was designed with no input parameters, no output parameters and no returned rows. The oracle procedure when called was just executing stuff. I modified the procedure to return (o_table out dbms_output.chararr) and then I could call it from tsql.
For others please reference the following url.
For others please reference the following url.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
