SQL*Plus not returning correct exit status

From: gareth <gareth.ekin_at_britannia.co.uk>
Date: Thu, 21 Feb 2008 03:26:34 -0800 (PST)
Message-ID: <87f71d73-2cf0-4383-9b68-3a2f2543cfd8@k2g2000hse.googlegroups.com>

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?? Received on Thu Feb 21 2008 - 05:26:34 CST