return code – Laurent Schneider

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”

Radoslav Rusinov August 25, 2005 at 11:24

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/

Laurent Schneider August 26, 2005 at 10:04

thank you very much! I am so pleased to be read 🙂

well, I did ask to be on orablogs.com in May, and it should be ok within a few weeks – they said. Well, I will send a reminder. I am on http://www.oracle.com/technology/community/opinion/index.html

Radoslav Rusinov August 26, 2005 at 15:34

Hi 🙂
I think now it can be done faster.
I wrote to Brain Duff to add me and he told me to send to him my blog RSS feed. Two days after that I was added to list. He sent me this link for some helpful instructions how to do it: http://thinkoracle.blogspot.com/2005/08/orablogs.html
If you have an RSS feed link just send it to him again and I think that this time you will be added faster.

Rado

Laurent Schneider August 29, 2005 at 11:17

done!

Don Drake May 22, 2007 at 22:37

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

Laurent Schneider Post authorMay 23, 2007 at 09:56

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

Bill Padfield October 12, 2007 at 19:31

This no longer appears to work in 10g – and I’m not sure why. Any suggestions?

Laurent Schneider Post authorOctober 12, 2007 at 19:43

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)

Pingback: return code and sqlplus | Laurent Schneider

Comments are closed.