#!/bin/ksh

###########################
#check for the oratab file. to get db name and info
###########################
ORATAB=/etc/oratab
LOGFILE=test_output.txt  # Set the logfile in one place so if it changes you have only one place to edit.
output=""                # Output from SQL/Plus goes here.

#
# set ORACLE SID and export path to use as client
#
ORACLE_SID=becsdev
ORAENV_ASK=NO
. oraenv

# Start SQL/Plus as a co-process.
sqlplus -S -NOLOG |&     ## Suppress normal headings and don't logon.

##  Exit SQL/Plus if any of the following signals are received:
##  0=normal exit, 2=interrupt, 3=quit, 9=kill, 15=termination
trap 'print -p "exit"' 0 2 3 9 15

# loop to get sid info
while IFS=',' read ORACLE_SID pass
do
   print '###################################################################################' >> $LOGFILE
   print "ORACLE SID IS SET TO $ORACLE_SID" >> $LOGFILE

   ##  This should have some error handling but for example purposes I assume it will work as expected.
   ##  Usually this is a bad assumption!
   print -p "connect system/${pass}@${ORACLE_SID}"  ## Send the connect command to the coprocess.
   print -p "@test.sql"
   print -p "DONE"
   while read -p output
   do
      if [[ $output == "DONE" ]]; then
        print "DONE" >> $LOGFILE
        break
      else
        # Throw all output into the logfile.
        print $output >> $LOGFILE
      fi
   done
   print '#####################################################################################' >> $LOGFILE
done < /home/sidlist

#  Close SQL/Plus.
print -p "exit"

exit 0