Geek asks and answers

Oracle stored procedure call with output parameter

I am working with SSIS 2008 and I have a problem with an Oracle stored procedure that has an output parameter.

I call the stored procedure in SqlPlus as follows:

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

The operators work, and I get the desired result. I am trying to do something similar in SSIS, but I need to do this several times, perhaps in ForEach or a script, to update the temporary result set with the result of calling the stored procedure (the stored procedure generates a number and I need to add this number to each line in a result set that just contains some status information).

I tried many different approaches and always ended up with "invalid" or similar errors.

I also tried the following approaches:

The main problem is the output parameter of the stored procedure.

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

+3
source share
4 answers

I came up with a solution that works:

  • Use the construct 'declare' and 'end'
  • Compatible with 'execute instant'
  • Add the using statement to the end of exec immediately to enter the 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 the Execute SQL task, set the task properties and it will work!

Oracle, : 1 - . sqlplus - sqlplus, @ .

: SSIS, .

+1

Oracle PLSQL, . http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm

Java, . Statement      java.sql.CallableStatement ps;      ps.registerOutParameter(Index, sqlType);

.Net Convictions. , .:)

+2

. Oracle SSIS,

Execute SQL SQL

declare
vresult number;

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

, SSIS , "" "0" ( )

PS: , Oracle SSIS

Mezue

0

More articles:


All Articles