HELP! Oracle Errors & UNIX Shell scripts
Date: 2000/08/10
Message-ID: <sp5l5upjn4t96_at_corp.supernews.com>#1/1
[Quoted] Hi All:
[Quoted] 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 ?
[Quoted] 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_at_%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}_at_${SERVER} @${SQL_PATH}/ddwTrunc.sql
cat ${DATA_PATH}/${DATA_ECP_COST} >> ecp_cost-`date +"%m-%d_at_%H:%M"`.bkup
exit ${ERR_ECP_COST_LOAD};
else
echo "SQL Loader worked okay for ECP_COST new data" sqlplus ${USER_ID}/${PASSWD}_at_${SERVER} @${SQL_PATH}/ecp_cost_load.sql
echo "Appending new data into ECP_COST" sqlplus ${USER_ID}/${PASSWD}_at_${SERVER} @${SQL_PATH}/ecp_cost_append.sql
echo "Running extract report"
sqlplus ${USER_ID}/${PASSWD}_at_${SERVER}
_at_${SQL_PATH}/ecp_cost_extract.sql
fi Received on Thu Aug 10 2000 - 00:00:00 CEST