Oracle + Unix question , Error validation for SQL statements in co process ?.

Oracle + Unix question , Error validation for SQL statements in co process ?.

Post by Ranjeesh K Ramakrishn » Thu, 22 Jul 2004 01:27:32



Hi,

If I use co process in a shell script , what is the best way to do the
error validation of the execution of any sql statements . I was trying
to change the following code to make use of co process concept. When I
tried echo $? after the execution of a "wrong statements"
it is still giving Zero output ( meaning success)

<<<<<<<<<<<<<<<<<<<<<<<<<<<

cat ${CFG_DIR}/srs_indx.lst | egrep -v '^#' | egrep -v '^[    ]*$' |
while read x
do

..

echo " Processing values :: pre $pre tbl $tbl indx $indx tblspc
$tblspc cols $cols param $param" >> ${LOGS_DIR}/srs_indx.log

sqlplus ${BIZSTG} << EOT >> ${LOGS_DIR}/srs_indx.log
set verify on timing on term on echo on feedback on serveroutput on

WHENEVER SQLERROR CONTINUE
drop index $indx;

WHENEVER SQLERROR EXIT FAILURE
alter session set query_rewrite_enabled = true;
create $pre index $indx on $tbl ($cols)
tablespace $tblspc
$param;
exit
EOT

RC=$?
if ( test $RC -ne 0 )
then
        ERR_MSG="ERROR in creating index $indx for table $tbl from

        echo $ERR_MSG >> ${LOGS_DIR}/srs_indx.log
        process_warning ${LOGS_DIR}/srs_indx.log
        exitstat=1
else
        echo "$indx created at `date`" >> ${LOGS_DIR}/srs_indx.log
fi

done                                  

Any help will be appreciated .

with thanks and regards
Ranjeesh K R