Re: SQL*Plus not returning correct exit status

On Feb 21, 7:05 pm, Anurag Varma <avora..._at_gmail.com> wrote:
> On Feb 21, 6:26 am, gareth <gareth.e..._at_britannia.co.uk> wrote:
>
>
>
> > Hello,
>
> > I have created the following piece of SQL:
>
> > whenever oserror exit sql.sqlcode rollback
> > whenever sqlerror exit sql.sqlcode rollback
>
> > set verify off
> > set pages 10000 lines 132
> > set serveroutput on format wrapped
> > set trimout on trimspool on
>
> > set termout off feedback off heading off
> > col current_time noprint new_value current_time
> > select to_char(sysdate,'YYYYMMDDHH24MISS') current_time
> > from dual
> > /
> > spool &3./&4._&1._&current_time..log
> > set termout on feedback on heading on
>
> > @&2./&1
>
> > prompt Logfile is: &3./&4._&1._&current_time..log
> > spool off
> > exit
>
> > The exit status is then read into a unix script
>
> > The problem is that certain types of SQL failure return an exit status
> > of 0
>
> > for example:
>
> > SQL> alter table TEST_TABLE modify (TEST_COLUMN VARCHAR2(2) NOT NULL);
> > alter table TEST_TABLE modify (TEST_COLUMN VARCHAR2(2) NOT NULL)
> > *
> > ERROR at line 1:
> > ORA-00904: "TEST_COLUMN": invalid identifier
>
> > Can anyone help with this??
>
> Won't work .. unix return codes are 8 bits long.
> So 904 would return 136 ... based on 8 leftmost bits.
>
> You would need to return "failure" .. and then have a script parse out
> the error
> code.
>
> Anurag

Agreed. I normally use something like:

whenever sqlerror exit 5
whenever oserror exit 10

If there is some specific Oracle error condition that the OS script needs to handle differently (though why would there be?) you could put the commands in a PL/SQL block and have it populate a SQL*Plus variable in an exception handler, then use that in the SQL*Plus exit clause.

What was the script going to do with the internal Oracle error codes anyway? Received on Fri Feb 22 2008 - 11:59:29 CST