Quote:
Originally Posted by
enigma_83
hi fndz.
Can you please help me with the code if I call a stored procedure from my shell script how to write the data which is returned from the procedure into a temprorary file and save it in .csv format.
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.