there is a myth of using sql.sqlcode in sqlplus
whenever sqlerror exit sql.sqlcode
this not ok. you should prefer whenever sqlerror exit failure or exit 1
Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88.
Let’s try it
$ sqlplus “/ as sysdba”
SQL> create user gaston identified by lagaffe quota 1k on users default tablespace users;
SQL> grant create table to gaston;
SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’
Disconnected from …
$ echo $?
1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!
well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable
SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.
SQL> def _rc
DEFINE _RC = “2″ (CHAR)
many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.
SQL> create or replace procedure p(o out number) is begin o:=1; end;
SQL> var rc number
SQL> exec p(:rc)
PL/SQL procedure successfully completed.
SQL> exit :rc
$ echo $?