Categories
Blogroll dba sqlplus

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

By Laurent Schneider

Oracle Certified Master

9 replies on “return code”

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

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

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.