How to store return values from a stored Procedure

dataservices job design
1

Hi All,

I am calling a stored procedure from a script as below:

DS_Source_Oracle.DS_ADMIN.PROC_CNTRCT1($var1,$var2,$var3);

It executes without any error, now just want to know how can I see or store the values of these OUT variables $var1,$var2,$var3…?

Thanks in advance…

BOBJFan (BOB member since 2011-09-24)

2

The parameters would be Data Services variables. You have to import the stored procedure into your Datastore and then drag the stored procedure from the Datastore into your script. It will work that way.

eganjp :us: (BOB member since 2007-09-12)

3

Hi eganjp,

thanks for your reply,
I have SQL Stored Procedure as:
create or replace PROCEDURE PROC_CNTRCT1 (OBJID OUT VARCHAR2, PSTDT OUT VARCHAR2, ORDPROD OUT VARCHAR2) IS
VAR_OBJID VARCHAR2(12);
VAR_PSTDT VARCHAR2(10);
VAR_ORDPROD VARCHAR2(15);

CURSOR CNTRCT_CURSOR IS
SELECT OBJECT_ID,POSTING_DATE,ORDERED_PROD FROM TMP_CONTRACT2;
BEGIN
OPEN CNTRCT_CURSOR;
LOOP
FETCH CNTRCT_CURSOR INTO VAR_OBJID,VAR_PSTDT,VAR_ORDPROD;
EXIT WHEN CNTRCT_CURSOR%NOTFOUND;
IF(VAR_PSTDT = ‘20120820’) THEN
dbms_output.put_line( VAR_OBJID||’ ‘||VAR_PSTDT ||’ '||VAR_ORDPROD );
END IF;
END LOOP;
CLOSE CNTRCT_CURSOR ;
END;

I imported this stored procedure in datastore and then opened a script and dragged this function to script, created 3 DS variable $var1,$var2,$var3 and assigned as 3 OUT parameters to the stored Procedure, executed the Job , it runs successfully but then what I don’t know is how can I capture these OUT parameter values in DS, when I run this stored Procedure I can see the result with DBMS output, I will like to know how can I see the same result in DS…

I did SQL trace for Stored Procedure and It was giving the trace log as:

Stored procedure call <PROC_CNTRCT1> is started.
SQL query submitted for stored procedure call <PROC_CNTRCT1> is: <BEGIN “DS_ADMIN”.“PROC_CNTRCT1”(“OBJID” => :OBJID,“PSTDT” =>
:PSTDT,“ORDPROD” => :ORDPROD); END;

.
Stored procedure call <PROC_CNTRCT1> output parameter has value of .
Stored procedure call <PROC_CNTRCT1> output parameter has value of .
Stored procedure call <PROC_CNTRCT1> output parameter has value of .
Stored procedure call <PROC_CNTRCT1> is done.

BOBJFan (BOB member since 2011-09-24)

4

It looks like it is doing exactly what you asked it to?

Call SP. SP executes cursor. Cursor loops until NOT FOUND (all variables null at this point). SP returns values of variables, which are null.

What are you actually trying to accomplish here, that might be a better way to address the problem. If you simply wish to populate 3 variables with values from the database in a script, perhaps try lookup_ext().

  • E

eepjr24 :us: (BOB member since 2005-09-16)

5

There are two problems here:

  1. You never assign any values to the output parameters. Something like OBJID := VAR_OBJID; is called for.
  2. A cursor inside a stored procedure is not going to give you a result set. The way the output parameters works you will only get a single value for each parameter.

eganjp :us: (BOB member since 2007-09-12)