###########################
#check for the oratab file. to get db name and info
###########################
ORATAB=/etc/oratab
#
# set ORACLE SID and export path to use as client
#
ORACLE_SID=becsdev
ORAENV_ASK=NO
. oraenv
# for loop to get sid info
for ORACLE_SID in `cat /home/sidlist|awk -F, '{print $1}'`
do
echo '###################################################################################' >> test_output.txt
echo 'ORACLE SID IS SET TO' >> test_output.txt
echo $ORACLE_SID >> test_output.txt
# get password info for each db
pass=`cat /home/sidlist|grep $ORACLE_SID|awk -F, '{print $2}'`
sqlplus "system/$pass@$ORACLE_SID" << EOF >> test_output.txt
@test.sql
EOF
echo '#####################################################################################' >> test_output.txt
done
the script works great....but when it loops to get the password for database name called kes....it picks ups kes12's password as well...a simple test is below....so now my question is how can i avoid that ?? and get the password for kes only and not both ??
cat /home/sidlist|awk -F, '{print $2}'|grep KES
abcd12
pass1
Try implementing something like this to save a few processes, useless use of cat and get the data you need at the same time, a row at a time (note this is untested)::
#!/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.
#
# set ORACLE SID and export path to use as client
#
ORACLE_SID=becsdev
ORAENV_ASK=NO
. oraenv
# loop to get sid info
while IFS=',' read ORACLE_SID pass
do
echo '###################################################################################' >> $LOGFILE
echo "ORACLE SID IS SET TO $ORACLE_SID" >> $LOGFILE
sqlplus "system/$pass@$ORACLE_SID" << EOF >> $LOGFILE
@test.sql
EOF
echo '#####################################################################################' >> $LOGFILE
done < /home/sidlist
You should read up on using SQL/Plus in a coprocess to save having to start it up once for every row in the sidlist file.
Also be care careful as I believe a ps -ef will show the username and password from the sqlplus command line which is a security issue.
I can't test this, but I would be interested in seeing if you like this way with a coprocess better. It is definitely more efficient than starting/closing SQL/Plus for every row in the sidlist file. Try running it with the "time" command and see if it is faster than how you were going to do it before. I have a coprocess example somewhere that I will post that also shows how to retrieve query results into shell variables. I think it would be good info to put out there. Coprocess example: http://www.unix.com/shell-programming-scripting/163684-sql-plus-coprocess-example-also-saves-query-results-into-shell-variables.html\#post302540429
#!/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