HELP! Oracle Errors & UNIX Shell scripts

From: Eisen Chao <echao_at_interaccess.com>
Date: 2000/08/10
Message-ID: <sp5l5upjn4t96@corp.supernews.com>#1/1

Hi All:

I've noticed that when I run Oracle SQLLDR and SQLPLUS that I really don't have a good way to trap run-time errors (e.g. "Ora-012345 Houston, We have a problem...").

I tried trapping problems using $? in kornshell, but my script just falls right thru.

Now I'm told that the exception handing for Oracle can only be seen within Oracle, even if I use stuff like features like RAISE_APPLICATION.

I'd really like to know if and when my stored procedures bomb out for example.

I've used Perl & DBI and I know that it can be done (at least within Perl), so why not the Korn shell ?

What's the trick here ?

Please don't tell me I have to do a grep of the system log file!

Thanks in Advance,

Eisen
Unicom
Chicago

sample code below:

echo "Now loading in Ecp_cost data..."

sqlldr USERID=${USER_ID}/${PASSWD}              \
CONTROL=${CTL_PATH}/${CTL_ECP_COST}             \
LOG=${LOG_PATH}/${LOG_ECP_COST}                 \
BAD=${BAD_PATH}/${BAD_ECP_COST}                 \
DATA=${DATA_PATH}/${DATA_ECP_COST}              \

DISCARDMAX=1000 # Any bad files from ECP_COST SQLLDR ? Move to date-stamped archive if [[ -s ${BAD_PATH}/${BAD_ECP_COST} ]]; then

   echo "Bad files for Ecp_cost found..."    echo "$(date) BAD file created during ECP_COST SQLLDR" >>
${LOG_PATH}/${LOG_ERR} &
   cat ${BAD_PATH}/${BAD_ECP_COST} >> ecp_cost-`date +"%m-%d@%H:%M"`.bad

fi

if [[ $? -ne 0 ]]; then
^^^^^^^^^^^^^^^^^^^^^^^

   # SQL Loader for ECP_COST not good, send msgs to error log    echo "$(date) Error occured during ECP_COST SQLLDR, RC = $($?)" >>
${LOG_PATH}/${LOG_ERR} &
   # ...Truncate Oracle tables, copy data to date-stamped backup    sqlplus ${USER_ID}/${PASSWD}@${SERVER} @${SQL_PATH}/ddwTrunc.sql

   cat ${DATA_PATH}/${DATA_ECP_COST} >> ecp_cost-`date +"%m-%d@%H:%M"`.bkup

   exit ${ERR_ECP_COST_LOAD};

else

   echo "SQL Loader worked okay for ECP_COST new data"    sqlplus ${USER_ID}/${PASSWD}@${SERVER} @${SQL_PATH}/ecp_cost_load.sql

   echo "Appending new data into ECP_COST"    sqlplus ${USER_ID}/${PASSWD}@${SERVER} @${SQL_PATH}/ecp_cost_append.sql

   echo "Running extract report"
   sqlplus ${USER_ID}/${PASSWD}@${SERVER} @${SQL_PATH}/ecp_cost_extract.sql

fi Received on Thu Aug 10 2000 - 00:00:00 CDT