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

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

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

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation


  1. Can you explain why “whenever sqlerror exit failure” does not get executed when a connection failure happens?

  2. which kind of connection failure?

    $ sqlplus /nolog

    SQL*Plus: Release - Production on Wed May 23 10:55:36 2007

    Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

    SQL> whenever sqlerror exit failure
    SQL> connect a/b
    ORA-12162: TNS:net service name is incorrectly specified

    $ echo $?

  3. correct
    Fortunately, it works with a 11g client!

    SQL*Plus: Release - Production on Fri Oct 12 19:42:00 2007

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    SQL> host exit 7

    SQL> def _RC
    DEFINE _RC = "0" (CHAR)

    SQL*Plus: Release - Production on Fri Oct 12 19:42:14 2007

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    SQL> host exit 7

    SQL> def _RC
    DEFINE _RC = "7" (CHAR)

Leave a comment

Your email address will not be published.