Call Oracle stored proc with output parameter

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 'inva开发者_JAVA百科lid 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?

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.:)

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.

check tis post: Run an Oracle package from SQL Server Integration Services

http://www.mssqltips.com/sqlservertip/2724/run-an-oracle-package-from-sql-server-integration-services/

regards

You are almost there. In order to retrieve the value of the output parameter from the Oracle stored procedure in SSIS, here is what worked for me

In the Execute SQL task, paste this in the SQL statement box

declare
vresult number;

begin
   my_stored_procedure(vresult);
   ?:=vresult;
end;

In the Parameter Mapping, ensure to map your SSIS variable to this output of your stored procedure by setting the direction to "Output" and parameter name as "0" (if it is the first parameter)

PS: ensure the Oracle output variable datatypes match your SSIS variables

Thanks Mezue