return code

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;

User created.

SQL> grant create table to gaston;

Grant succeeded.

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 $?
0

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;
2 /

Procedure created.

SQL> var rc number
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> exit :rc
Disconnected ...
$ echo $?
1