Hi Tom,

One question about whenever sqlerror - can it be used to selectively suppress/ignore oracle errors? I prepared this DDL implementation script for the DBAs, now they come back and say that their "policy" is that every "create table"/"create index" should precede with the "drop" statement; and the script should ignore the errors triggered by the "drop" command, but it should stop if any errors are returned by the "create" command. In short, they want to ingnore ora-942, ora-1418 and ora-955 errors only (the script should continue upon receiving these errors), but should stop upon receiving other errors.

I dont know if this can be done without pl/sql. When I asked them if they could tell me how to do it -they dont reply. Do you have any solution for this? (short of hiring a sniper to take down my entire dba team...)! Thanks.

ENV_HAR_DATABASE=bacshar
HAR_PROCESS=har_transform.ksh
ENV_DATABASE=ONE/TWO@THREE
TSTAMP=`date "+%d-%m-%Y %H:%M:%S"`
TRANS_LOG_MSG="####<${TSTAMP} o'clock GMT><2><Hard Archiving><`uname -n`><N/A><${HAR_PROCESS}><${ENV_HAR_DATABASE}><Hard Archiving><N/A><HAR ALERT_CODE Ha1016 APP_MESSAGE 2 Transform Unsuccessful>"

sqlplus -s /nolog <<EOF
connect ${ENV_DATABASE}
set feedback off
whenever sqlerror exit 1
exec P_TRANSFORM('$PROCESS_NAME','$JOB_TYPE');
exit 0;
EOF

if [ $? -ne 0 ] ; then
echo "An error was encountered." >> har_${PROCESS_NAME}_${JOB_TYPE}_err.log
echo "An error was encountered."
echo $TRANS_LOG_MSG >>$ENV_HAR_EMS_LOG_FILE
exit 1
fi


In the above case, I want to return 1 if I am not able to connect to oracle (due to tnsentry failure or oracle is not available) But failed to achive using above script. Any suggestions ?

It works fine if you put

whenever sqlerror exit 1

before

connect ${ENV_DATABASE}