Home » Infrastructure » Unix » Return sqlplus error to calling Unix shell [Merged]
Return sqlplus error to calling Unix shell [Merged] [message #352839] Thu, 09 October 2008 20:49 Go to next message
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
whenever doesn't trap properly [message #352840 is a reply to message #352839] Thu, 09 October 2008 21:05 Go to previous message
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
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:
  


Current Time: Tue Dec 06 02:26:49 CST 2016

Total time taken to generate the page: 0.05427 seconds