How to exit sqlplus when error happens

I would like to catch any sql error that could happen so I wrote this in the ksh :

$ORACLE_HOME/bin/sqlplus -s u/p <<EOF
    WHENEVER OSERROR EXIT 68;
    WHENEVER SQLERROR EXIT SQL.SQLCODE;

    CREATE TABLE new_table
    AS (SELECT * FROM wrong_old_table);
    COMMIT;
EOF

I put a wrong name for the old table to see what happens. I expect to have the sqlcode only like I ask in WHENEVER SQLERROR but I have this :

 AS (SELECT * FROM wrong_old_table)
                      *
 ERROR at line 2:
 ORA-00942: table or view does not exist

I changed the code :

 $ORACLE_HOME/bin/sqlplus -s u/p <<EOF
    WHENEVER OSERROR EXIT 68;
    WHENEVER SQLERROR EXIT SQL.SQLCODE;

    BEGIN
       CREATE TABLE new_table
       AS (SELECT * FROM wrong_old_table);
       COMMIT;
    END;
 EOF

 sql_code=$?

 echo "code=$sql_code"

Even though there is an error, the code equals 0. Where is the sql error code ?

Solution

I'm not sure what you're doing, but this type of code works fine for me. For your first example:

SQL> whenever sqlerror exit sql.sqlcode;
SQL> create table new_table as select * from wrong_old_table;
create table new_table as select * from wrong_old_table
                                        *
ERROR at line 1:
ORA-00942: table or view does not exist

$ echo $?
174

Note that the exit code does not correspond exactly to the SQLCODE because the SQLCODE is always negative while the exit code in most shells are limited to values from +0 to +255; thus, it appears that what you get as an exit code is the low order byte of the two's complement of the SQLCODE (+). In practical terms, this means you get a non-zero value which is predictable and repeatable, but which isn't going to match the SQLCODE exactly, and where multiple SQLCODE values may produce the same exit code value.

Best of luck.

(+) If you care: in the example above the SQLCODE is -942. In hexadecimal this is 0xFFFFFC52. Inverting all the bits of this number gives you the one's complement, which is 0x3AD. Adding one to this value gives you the two's complement value, which is 0x3AE. The low-order byte of this is 0xAE, which is 174 in base 10. I've repeated this for SQLCODE values of -904, which gives an exit code of 136, and -6550, which gives an exit code of 150.