Re: SQL*Plus not returning correct exit status

On Feb 21, 3: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??

Try using the WHENEVER SQLERROR to trap errors and then an EXIT command to return whatever code you want. Put this before your select statement.

WHENEVER SQLERR http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12052.htm#i2700066 EXIT http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12023.htm#i2697968

example

WHENEVER SQLERROR EXIT SYS.SQLCODE
or
WHENEVER SQLERROR EXIT FAILURE Received on Thu Feb 21 2008 - 09:25:01 CST