How to stop sqlplus command in unix shell script when any sql fails

Question

I am new to shell script . i am writing a code for DBA automation in which i have master.sql file have declarations of n number of sqls . and these sqls are placed inside particular folder . i have a script which is calling this master.sql and executing those sqls at once indirectly and saving in the log file

sqlplus command is saving the output of all the sqls declared inside master.sql at once wwhat i want is it must stop the execution when the first sql fails and return the error witht the specific sql which failed or ask to rollback and asking do you want to continue or not ? tried using sqlplus whenever,, sqlerror ,$? nothing worked

sqlplus  ${USER}/${PASS}@$SID  @master.sql > /home/deploy/vidya/properties/result/result.log

Now what i am trying to do is .... i want to stop the execution of sqls when any of the sql fails from the starting and prompts on the command line with the error and specify which sql fails

Answer 1

One idea would be to launch the sql's in sequence and check them afterwards for errors: e.g.

sqlplus  ${USER}/${PASS}@$SID  @dba_demo.sql > /home/deploy/vidya/properties/result/result_dba_demoSQL.log

checker = $(grep "ERROR" result_dba_demoSQL.log)

if [ test -z $checker ]

   then echo "dba_demo_SQL successfull run"

   else

       echo "dba_demo_SQL failed"

       exit 0

fi

The exit 0 part, exits the whole script when grep find an error in the log file.