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

9 thoughts on “return code”

  1. 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: http://www.orablogs.com/orablogs/

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

  3. which kind of connection failure?

    
    $ sqlplus /nolog
    
    SQL*Plus: Release 10.2.0.2.0 - 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
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    
    $ echo $?
    1
    

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

    
    SQL*Plus: Release 10.2.0.3.0 - 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 11.1.0.6.0 - 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 Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>