Hai Hema,
Spool concept is not required in the shell scripting. Simply you can create a blank .csv file using `touch` command in unix.
Spool concept is useful only in Windows. what ever you are passing the sqlfile.sql( Input to Script )those are declared in the SQL-Part (Begin - - End
of the below mentioned code (Execute simple Procedure):
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.