Exit when sql script give error

UNIX for Advanced & Expert Users
1

Hi Guys,

I am calling a SQL script which runs under while loop, i need to exit the while loop if i get error in sql script which is called

while [i -le 10] 
do
sqlplus -s user/pass@db @test.sql id$i
done

test.sql
whenever sqlerror exit;
alter table t1 add &1 number;

I need to come out of while loop if i find error in any of the calling test.sql script with error message stating test.sql failed for 1st loop hence exiting loop

2

With some corrections - mind your spaces around [[ and [ :
try:

status=0
while [[ $i -le 10 && $status -eq 0 ]] 
do
sqlplus -s user/pass@db <<-EOF
@test.sql id${i}
EOF
status=$?
if [ $status -ne 0 ] ; then
   echo "script failed on $i"
fi
done

test.sql code:

# test.sql
whenever sqlerror exit failure;
alter table t1 add &1 number;
exit 0
3

Thanks,

is there a way exit the loop with error message if there is any DB connection issue for eg

sqlplus -s db/user@db

ORA-12154: TNS:could not resolve the connect identifier specified