Parsing Oracle errors in script

Dev Shed Forums Sponsor:

July 27th, 2005, 01:54 PM

jabs

Registered User

Join Date: Feb 2004

Posts: 19

Time spent in forums: 3 h 51 m 26 sec
Reputation Power: 0

Parsing Oracle errors in script

Hi everyone.

I have a very simple script that uses oracle's 'imp' command to import data into a database. Once the process is finished, it emails me using mailx. Occasionally, it fails. I would like to be able to know when it fails and send a different email in case of failure. I know how to capture Oracle failures in PL/SQL, but not in unix. Can anyone help?

Here is a snippet of code: (As i mentioned, it's very simple)

**************

imp username/pass@db buffer=......TABLES=TABLENAME

mailx -s "table imp" Joe <<+++
table imported.
+++

***************

Thanks for your help!

July 27th, 2005, 02:18 PM

stdunbar

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: May 2004

Location: Superior, CO, USA

Posts: 1,730

Time spent in forums: 1 Month 2 Days 23 h 21 m 6 sec
Reputation Power: 375

At a guess the command will exit with a different value if it fails. In traditional Unix programs a zero exit value means that everything went ok. I find Oracle's docs almost impossible to navigate so a quick look on OTN didn't tell me what the exit values for imp are when it fails but you would usually want to do something like:

Code:

RETVAL=0
imp username/pass@db buffer=......TABLES=TABLENAME
RETVAL=$?
if [ $RETVAL = 0 ] ; then
    mailx -s "table imp" Joe <<+++
    table imported.
    +++ 
elif [ $RETVAL = 1 ] ; then
    mailx -s "table imp" Bob<<+++
    table import failed.
    +++ 
fi

Now, having written this, please understand that I'm not the worlds expert on shell scripts but something like this should be close.

Last edited by stdunbar : July 27th, 2005 at 02:24 PM. Reason: Fixed some wierdness on the OTN link

July 27th, 2005, 02:45 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,308

Time spent in forums: 1 Week 3 Days 5 h 20 m 41 sec
Reputation Power: 48

imp does in fact return ZERO on sucess and non-zero on failure (almost always 1).

Code:


if [ $RETVAL -eq 0 ] ; then
    mailx -s "table imp" Joe <<+++
    table imported.
    +++
else

    mailx -s "table imp" Bob<<+++
    table import failed.
    +++
fi

July 27th, 2005, 03:28 PM

jabs

Registered User

Join Date: Feb 2004

Posts: 19

Time spent in forums: 3 h 51 m 26 sec
Reputation Power: 0

Quote:

Originally Posted by stdunbar

Code:

RETVAL=0
imp username/pass@db buffer=......TABLES=TABLENAME
RETVAL=$?
if [ $RETVAL = 0 ] ; then
    mailx -s "table imp" Joe <<+++
    table imported.
    +++ 
elif [ $RETVAL = 1 ] ; then
    mailx -s "table imp" Bob<<+++
    table import failed.
    +++ 
fi

Now, having written this, please understand that I'm not the worlds expert on shell scripts but something like this should be close.

Ok - I see what you're doing - if the error code is something different, I'll try to find it. Thanks a lot for your help!!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: