Shell Programming and Scripting

run_query() 
{ 
    # $1 - db login 
    # $2 - user defined error code 
    # $3 - SQL to execute 
    # $4 - logfile to re-direct to 
$SQLPLUS -s $1<<EOF | sed '/^$/d' > $4 
  $3; 
EOF 

check_for_sqlplus_error $? "code "$2 
return 0 
}

run_query $ODS 200 "
  set HEADING OFF FEEDBACK OFF TRIMOUT ON TRIMSPOOL ON
  select distinct
	batch_num 
  from	cr_batch
  where	load_desc = '$SOURCE_SYSTEM_DESC';
"  $LOGPATH$SOURCE_SYSTEM_DESC.batch.lst 

...
run_query $STAGING 300 "
  set pagesize 0 AUTOT ON serveroutput on SIZE 50000
     exec p_stage_load('$SOURCE_SYSTEM_DESC', $LOAD_RUN_NUM, $BATCH_NUM );
"  $LOGPATH$SOURCE_SYSTEM_DESC.$BATCH_NUM.log & 
...

# Read whole lines
IFS='
'
set -A RESULTS $({
    sqlplus -s /nolog "
        connect un/pw
        select or exec ...;
"
    print RC=$?
} 2>&1)

if [[ "RC=0" != ${RESULST[(( ${#RESULTS[@]} - 1} ))] ]]
then
    write_log "sqlplus error"
    exit 1
fi

for i in ${RESULTS[@]}
do
    case $i in
        ORA*|SP2*|PLS*)
            write_log "sql error"
            exit 1
        ;;
        ... whatever else you want to look for ...
        ;;
    esac
done

set -A RESULTS $({
    sqlplus -s /nolog "
        WHENEVER SQLERROR EXIT 1    <== Forgot to include my earlier recommendation
        connect un/pw
        select or exec ...;
"
    print RC=$?
} 2>&1)


My results array will consist of elements (whole lines of text) starting from 0.  Remember that I manufactured
my own message following the sqlplus to show sqlplus' exit code (RC=$?).  This sits in the array's last element.
Since we don't typically know how many rows will return I have to compute the size of the array before knowing
which element is the last.

${#RESULTS[@]} gives me the array's size in terms of the number of elements and (( ${#RESULTS[@]} - 1} )) 
points me to the array's last element (remember that the array starts with at element 0).  Therefore, the "if"
statement compares a known "good" exit code message of "RC=0" with the actual message returned following sqlplus'
execution.  If the message is not "RC=0" it must be bad and sqlplus exited with an error so we will exit too.
if [[ "RC=0" != ${RESULST[(( ${#RESULTS[@]} - 1} ))] ]]
then
    write_log "sqlplus error"
    exit 1
fi

This loop simply processes each element in the array and tests each array element for whatever we want.  "ORA*|SP2*|PLS*)"
are known Oracle error messages so we typically want to handle them.
for i in ${RESULTS[@]}
do
    case $i in
        ORA*|SP2*|PLS*)
            write_log "sql error"
            exit 1
        ;;
        ... whatever else you want to look for ...
        ;;
    esac
done

IFS=' '