Oracle Apps: Sample Concurrent Program Shell script to call multiple SQL scripts

In one of my project, the client to wanted to have a Host Concurrent Program, which calls multiple sql statements. It was easy to create one shell script which calls multiple sql statements. But the client wanted the concurrent program to fail (complete in ERROR status), if any of the sql statements fail (not just the last one).

A sample code which we used in this scenario:

 #!/bin/ksh  
 
 #Do whatever you want here  
 echo Connecting to apps...  
 
 sql_output=`sqlplus -s /nolog <<EOF  
 
 connect apps/$PASSWORD  
 
 prompt getting sysdate value into a variable...  
 COLUMN today NOPRINT NEW_VALUE g_today  
 SELECT to_char(sysdate,'DD-MON-YYYY') today  
 FROM dual;  
 
 set serveroutput on size 1000000  
 
 prompt get the count from debug table...  
 select count(*) from xxaj_debug;  
 
 prompt truncate debug table...  
 truncate table xxaj_debug1;  
 
 prompt populate temporary table...  
 insert into xxaj_debug values (xxaj_seq.nextval, 'Anoop', sysdate);   
 
 update xxaj_debug  
 set msg = 'Anoop Johny';  
 
 COLUMN old_count NOPRINT NEW_VALUE g_old_count  
 select count(*) old_count from xxaj_debug;  
 delete from xxaj_debug;  
 
 EOF`  
 
 if [ `echo "$sql_output" | egrep 'ORA-[0-9]{5}:' | wc -l` -ne 0 ]   
 then   
 echo "$sql_output"   
 exit 1   
 else   
 echo "$sql_output"   
 exit 0  
 fi   

The above script with exit with status 0 if every sql statement is success.

Now if any of the statement fails, it will return with status 1.

Feel free to point out if anything is missing/wrong in this blog.