script or piece of code where the data returned by a stored procedure you are writing

Shell Programming and Scripting
1

hi fndz.

Can you please help me with the code if I call a stored procedure from my shell script and stored procedure returns a cursor,
cursor output should be saved to a file

2

RETVAL=`sqlplus -s USERNAME/PASSWORD@DBNAME <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare
OUT_STATUS NUMBER;
OUT_MSG VARCHAR2(200);
Begin
ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG);
dbms_output.put_line ('KeepThis '||OUT_STATUS ||' '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`

echo " " >> $USER_LOG
echo "Procedure: ODS_SP_REMOVE_PRE_SUB_DUP output is: " >> $USER_LOG.CSV
echo "OUT_STATUS= $X" >> $USER_LOG.CSV
echo "OUT_MSG= $Y " >> $USER_LOG.CSV

Note: You need to create the CSV file before call the Oracle Procedure.

3

I call my stored procedure in DB2...how can i write the RETVAL on to a file

4

First you will display your Stored Procedure output.

In Oracle, i am using dbms_output.put_line
In DB2, ??

Along with output of Stored Procedure, I will pass the 'KeepThis ' string to the RETVAL

In RETVAL it will give the:

Eg:
RETVAL= KeepThis 1 Failed or RETVAL= KeepThis 0 Success