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

9 thoughts on “return code

  1. Radoslav Rusinov

    Hi Laurent,
    I am reading your blog regularly, it is very useful and your posts demonstrates valuable technical findings and solutions.
    I think that it can be more helpful for the Oracle community if more people know about it.
    Did you think to add it to some of the Oracle Blog lists, for example:

  2. Don Drake

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

  3. Laurent Schneider Post author

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

  4. Laurent Schneider Post author

    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)

  5. Pingback: return code and sqlplus | Laurent Schneider

Comments are closed.