Call Oracle stored proc with output parameter

Question

I'm working with SSIS 2008 and am having a problem calling an Oracle stored procedure that has an output parameter.

I call the stored procedure in SqlPlus like this:

var vresult number;
exec my_stored_procedure(:vresult);
print vresult;

The statements work and I get the output I need. I am trying to do something similar in SSIS, yet I need to do this repeatedly, maybe in a ForEach or a script to update a temporary result set with the result of calling the stored procedure (the stored procedure generates a number, and I need to add that number to each row in a result set which just holds some state information).

I have tried a lot of different approaches and always end up with 'invalid statement' or similar errors.

I have also tried the following approaches:

1. How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider?

2. Update a row in oracle using OLEDB command(SSIS)

3. Oracle variables

The crux of the problem seems to be the stored procedure's output parameter.

I have tried using the the Oracle Provider for OLE DB. Any ideas?

Answer 1

If you are trying to invoke The stored Procedure in Oracle PLSQL this Link is very brief. http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm

If you are Working in Java then. The Statement Object java.sql.CallableStatement ps; ps.registerOutParameter(parameterIndex, sqlType);

Similarly .Net or Any Other Platform must will have the same Convictions. Hope so.:)

Answer 2

I came up with a solution that works:

• Use the 'declare' and 'end' construct
• Combine with 'execute immediate'
• Add the 'using' statement to the end of the exec immediate to inject variable

So a script that implements this might look something like this:

declare
myVar number;
myStatement varchar2(50);
begin
    myStatement:='exec myProc(:1)';
    execute immediate myStatement using output myVar;
end;

Paste this script into an Execute SQL task, set the task's properties and it works!

I'm new to Oracle but it looks like the :1 notation is a place-holder for the variable. You can test this using sqlplus too - just save the code in a file and start sqlplus using the @ option on the command line.

The only problem: I can't get value of the variable for use in SSIS, but that's another problem.