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

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

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

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

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

Leave a Reply

Your email address will not be published.