Hi all,

I am having problem with a shell script. I have a couple of csv files. The shell script will do some operation on them, create a sql file which will then be called by sqlplus. The problem is to gracefully exit sqlplus at the end of every operation as I do not want to hang on to the connection. But I am getting an error the way I am doing it (see code). Can someone pl. help?


#!/bin/ksh

if [ -e *.csv ]
then

for csvfile in *.csv
do
echo "set feedback off" > temp.sql
echo "set echo off" >> temp.sql
echo "delete from test_table;" >> temp.sql
awk -F, -f parse.awk $csvfile >> temp.sql
echo "commit;" >> temp.sql
rm -f $csvfile

sqlplus -S uanme/pwd@mydb @temp.sql > test.log <<
endsqlplus
done

endsqlplus
exit 0

fi

i think you could append exit at the end of the .sql file

and then just execute the .sql file by using:

sqlplus -S uanme/pwd@mydb @temp.sql > test.log
in your loop, you would probably want to rename your log file by concatenating the csvfile to it, just an idea hop it helps



i.e.

#!/bin/ksh

if [ -e *.csv ]
then

for csvfile in *.csv
do
echo "set feedback off" > temp.sql
echo "set echo off" >> temp.sql
echo "delete from test_table;" >> temp.sql
awk -F, -f parse.awk $csvfile >> temp.sql
echo "commit;" >> temp.sql
echo "exit;" >> temp.sql
rm -f $csvfile

sqlplus -S uanme/pwd@mydb @temp.sql > test.$csvfile.log
done

exit 0

fi

That's right...you append it within the sql fiel and you'll be fine. I've used approach this before.

Try this.

#!/bin/ksh

if [ -e *.csv ]
then

for csvfile in *.csv
do
echo "set feedback off" > temp.sql
echo "set echo off" >> temp.sql
echo "delete from test_table;" >> temp.sql
awk -F, -f parse.awk $csvfile >> temp.sql
echo "commit;" >> temp.sql
echo "exit;" >> temp.sql
rm -f $csvfile

sqlplus -S uanme/pwd@mydb <<EOF
@temp.sql
exit 0
EOF
done
fi

Good Luck!

Thanx for all the suggestions. The exit thing works perfectly fine.