OraFAQ Forum: Unix » Return sqlplus error to calling Unix shell [Merged]

Home » Infrastructure » Unix » Return sqlplus error to calling Unix shell [Merged]

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Return sqlplus error to calling Unix shell [Merged] [message #352839]

Thu, 09 October 2008 20:49

podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD

Junior Member

I posted this earlier for a different question.
I have a database procedure that will produce an error.
How can I trap that error in the calling Unix script?
This is how I call sqlplus from the unix script

  sqlplus -s username/password <<eof
    whenever SQLERROR exit 2 rollback
    whenever OSERROR exit 3 rollback
    exec database_utils.test_error;
  eof

The procedure is

  PROCEDURE test_error IS
  BEGIN
    raise NO_DATA_FOUND;
  END;

I am using Oracle 10g and AIX for the shell scripting.
I have already researched spooling the output to a file and then parsing the file in Unix. Also I had thought to add an OUT parameter to the procedure, but I have approx 100 procedures to modify then.

Thank you in advance

Report message to a moderator

whenever doesn't trap properly [message #352840 is a reply to message #352839]

Thu, 09 October 2008 21:05

podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD

Junior Member

I have a third question regarding this process.
I have a database procedure that will produce an error.

The procedure is

  PROCEDURE test_error IS
  BEGIN
    raise NO_DATA_FOUND;
  END;

This is how I call sqlplus from the unix script

sqlplus -s username/password <<eof
whenever SQLERROR exit 2
whenever OSERROR exit 3
exec database_utils.test_error
eof
  ERRORCODE=$?
  echo $ERRORCODE

In this case the ERRORCODE is 3

If I change the code to this

sqlplus -s username/password <<eof
whenever OSERROR exit 3
whenever SQLERROR exit 2
exec database_utils.test_error
eof
  ERRORCODE=$?
  echo $ERRORCODE

the ERRORCODE is 2.

It seems that the whenever statement is not reacting prolerly. It always takes the exit statement from the last whenever code. Is there something wrong with my script?

I am using Oracle 10g and AIX for the shell scripting.

Thank you in advance

Report message to a moderator

Previous Topic:	How to split current existing 32G filesystem into 8G*4 in Solaris 9?
Next Topic:	root.sh and rootinst.sh execution in oracle installation-why required

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:30:49 CST 2024