Oracle Linked Server to call procedure or function
-
I have a linked server that connects to an Oracle Server.
On the oracle server they did this:
Both the procedure and function both run a simple select statement. There are no parameters at the moment. I have been trying to run either of those from my sql server. I cant figure it out. I have googled all I can before I came to ask here.
Ive tried a few things:
EXECUTE ('begin TARR_WLF_API.engsys.test_procedure; end;') at EMDP
SELECT * FROM Openquery(EMDP,'{CALL TARR_WLF_API.engsys.test_procedure}')
SELECT * FROM Openquery(EMDP,'SELECT * FROM TARR_WLF_API.engsys.test_procedure()');I am always getting errors. I know i can get the data, because if i run just the select statement that the procedure actual runs, i get results. Here is the simple select
SELECT * FROM Openquery(EMDP,'select * from engsys.tmse_cctr_lt')Please help
-
The problem here is that you are trying to do something that the connection via Linked Server (which is either OLE DB or ODBC), doesn't support doing. You would be better served by having a stored procedure on the Oracle side perform the task of running that package, and at least then, you are getting a standard form of output that can traverse the Linked Server connection. If you can get the stored procedure to do what needs doing to just return a standard result set, you'll be good to go.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, September 11, 2018 11:24 AM
The stored procedure IS on the oracle side. Both test_procedure and test_function or on the oracle side
- dmbexodus - Tuesday, September 11, 2018 1:52 PM
okay, so is the stored procedure what you execute? And if it is, does it's output come from a SELECT? If it calls the package, that might not be viable, as it returns a ref-cursor, and that's not going to work. If it instead accessed the ref-cursor and SELECTed the rows in it, that would probably work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
We were just trying to figure out if I could get the data from the oracle database in sql server. So we tried a stored procedure and a function. It doesnt matter which we use on the oracle side as long as we get the data in sql server. This was just a simple test to see how to do it. The stored procedure on the oracle side will be much more complex and have parameters, but we wanted to figure out the best way before moving forward.
Ok, so the stored procedure on the oracle side is test_procedure. That procedure is a simple
select * from engsys.tmse_cctr_ltand that is all.I just want to execute the stored procedure and get the results on the sql server side.
- dmbexodus - Tuesday, September 11, 2018 2:09 PM
For the OpenQuery select, try something like:
SELECT PackageName.ProcedureName FROM DUAL;When using EXEC...AT, I don't think you include the begin, end. So you just use something like:
EXECUTE ('Call ProcedureName()') at LINKED_SERVER_NAMEI just can't remember if I had to include the package name when I did those before so play around with include/exclude the package name.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply