Shell Programming and Scripting

sqlplus -S <<EOF > /dev/null
user/password@universe
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;



SET ECHO ON
SET SERVEROUTPUT ON
SPOOL $LOGFILE

-------------------------------------------------------------------------------------
---- Here I need to make a loop to read text file and pass on the first line as 
---- parameter to following .sql script till all the rows are not finished in text 
---- file
-------------------------------------------------------------------------------------
@cancel.sql 

SPOOL OFF
EOF



sql_return_code=$?

if [ $sql_return_code != 0 ]
then
echo "The Cancel.sql script failed. Please refer to the log results.txt for more information"
echo "Error code $sql_return_code"
exit 0;
fi

sqlplus user/password@universe < QUERIES.sql > Output_file

cat cancel.txt
USER_NAME= Singh
Password= xyz
universe= abscedd

cat test_script.ksh
user=`awk -F"=" '/USER_NAME/ {print $2}' cancel.txt`
password=`awk -F"=" '/Password/ {print $2}' cancel.txt`
universe=`awk -F"=" '/universe/ {print $2}' cancel.txt`
sqlplus "$user"/"$password"@"$universe" < QUERIES.sql > Output_file
 
you can do testings here with Output file as per your reqiurements.

#!/bin/bash
# read first line holding the parameter until there is nothing more to read
while read CANCEL_ID; do   
   sqlplus -s user/password@universe @cancel.sql $CANCEL_ID
   sql_return_code=$?
   if [ $sql_return_code != 0 ]
   then
      echo "The Cancel.sql script failed. Please refer to the log results.txt for more information"
      echo "Error code $sql_return_code"
      exit 0;
   fi
   # read second line and ignore it
   read DUMMY                     
   # read 3rd line and ignore
   read DUMMY
   # read empty line between the paragraphs and ignore
   read DUMMY                  
# feed the file to the loop
done <Cancel_ID.txt