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
– tbone, Commented May 10, 2011 at 19:06
Yes I set the parameter direction to output and have checked that about a thousand times by now :) — Erik Westermann
– Erik Westermann, Commented May 10, 2011 at 20:54

Ahsan · Accepted Answer · 2011-05-10 14:46:38Z

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, 2011 at 14:46

Ahsan

515 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erik Westermann Over a year ago

Thanks for your reply. I already have this information and my question relates specifically to SQL Server Integration Services (SSIS).

Erik Westermann · Accepted Answer · 2011-05-12 19:28:35Z

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.

Jason Marshall · Accepted Answer · 2013-01-16 14:27:25Z

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, 2013 at 14:27

Jason Marshall

3,3871 gold badge14 silver badges3 bronze badges

Comments

marc_s · Accepted Answer · 2016-12-17 22:07:46Z

0

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

edited Dec 17, 2016 at 22:07

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 26, 2016 at 8:12

Mezu Obi

111 bronze badge

Collectives™ on Stack Overflow

Call Oracle stored proc with output parameter

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related