Re: SQL*Plus not returning correct exit status

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 Received on Thu Feb 21 2008 - 13:05:19 CST