You are now on my old blog. Please update your bookmarks to my new blog

23 August 2005

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


Blogger Radoslav Rusinov said...

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:

25/8/05 11:24  
Blogger Laurent Schneider said...

thank you very much! I am so pleased to be read :-)

well, I did ask to be on in May, and it should be ok within a few weeks - they said. Well, I will send a reminder. I am on

26/8/05 10:04  
Blogger Radoslav Rusinov said...

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:
If you have an RSS feed link just send it to him again and I think that this time you will be added faster.


26/8/05 15:34  
Blogger Laurent Schneider said...


29/8/05 11:17  

Post a Comment

<< Home