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 hereechoConnecting 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 ]thenecho"$sql_output"exit1elseecho"$sql_output"exit0fi
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.
