execute Oracle stored procedure from SQL Server 2012

0
votes

I have changed variable names to protect customer.

I am trying to execute a stored procedure from a remote Oracle 12c database, from my SQL Server 2012 database. If it helps, the Oracle database pl/SQL procedure is has input data types of: INPUT1=CHAR(200), INPUT2=(NUMBER), OUTPUT=SYS_REFCURSOR

I am constantly getting the following error:

"OLE DB provider "OraOLEDB.Oracle" for linked server "testsrv1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.""

My query is found below: 

DECLARE @outputParameter int
DECLARE @inputParameter varchar(400)
DECLARE @inputParameter1 varchar(400)

set @inputParameter1 = 'SampleStringValue'
set @inputparameter = SampleNumberValue

EXEC ('BEGIN storedProcedure(?,?); END;', @inputParameter, @inputParameter1, @outputParameter OUTPUT) at testsrv1
2
sql-serveroracle
It looks like you are missing @ in front of inputParameter1 on your last line. Is it that simple or is that just a typo in your code sample? – Jeremy J.
That was a typo. I'll fix now. – proximo
I've never done this before, which is why I am commenting instead of writing an answer. From googling a little, it seems like you need a 3rd ? for the output parameter. – Jeremy J.
That helped I think. Now I am getting error: OLE DB provider "OraOLEDB.Oracle" for linked server "testsrv1" returned message "ORA-06550: line 1, column 13: PLS-00222: no function with name 'GETALL' exists in this scope ORA-06550: line 1, column 7: PL/SQL: Statement ignored". – proximo
GetAll is the last part of the PL/SQL procedure. – proximo

2 Answers

0
votes

After many hours of research, I found that you cannot return a SYS_REF_CURSOR data type over a database link. Even from an Oracle database to another Oracle database.

https://asktom.oracle.com/pls/apex/asktom.search?tag=returning-result-set-from-stored-procedure-over-a-database-link

If Oracle database A is the DB producing the SYS_REF_CURSOR, and database B is the consumer - the only way that I have found online is to retrieve the data inside database A and store into a table, then pass the table via database link.

Is this really the only way to do this? I am somewhat new to oracle, but this seems like something that should be able to be done by now. The link from above is from 10+ years ago.

0
votes

You can use SSIS and the SSIS Data Streaming Destination to connect to Oracle in SSIS and consume the output of the SSIS package in a SQL Query.

From SSIS you can use OleDB, ODBC, ADO.NET connectors for Oracle, or you can use a .NET script to connect to Oracle.