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:

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?

Been a while for me in SSIS, but did you set your parameter Direction to Output? — tbone, May 10 '11 at 19:06
Yes I set the parameter direction to output and have checked that about a thousand times by now :) — Erik Westermann, May 10 '11 at 20:54

score 2 · Answer 1 · answered May 10 '11 at 14:46

2

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

answered May 10 '11 at 14:46

Ahsan

51
5

1

Thanks for your reply. I already have this information and my question relates specifically to SQL Server Integration Services (SSIS). – Erik Westermann May 10 '11 at 14:49

score 1 · Accepted Answer · answered May 12 '11 at 19:28

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.

score 0 · Answer 3 · answered Jan 16 '13 at 14:27

0

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

answered Jan 16 '13 at 14:27

Jason Marshall

3,289
1
12
3

score 0 · Answer 4 · edited Dec 17 '16 at 22:07

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

Call Oracle stored proc with output parameter

4 Answers4

Related