Skip to Main Content
  • Questions
  • How to get the ORA-01652 error code in Unix

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, thierry.

Asked: April 02, 2001 - 11:21 am UTC

Last updated: January 03, 2005 - 11:19 pm UTC

Version: Oracle 8i

Viewed 10K+ times! This question is

You Asked

Hi Tom,

In order to pass an Oracle error to Unix, I use the "whenerror..." statement.
This works fine for some kind of errors (like division by zero, invalid SP name, ...), BUT NOT FOR ALL.
In particular, when the error ORA-01652 (segments too small) is raised in Oracle, it is not passed to Unix.

Is there a way to raise this error at the Unix level ?

Thanks,
Thierry.



and Tom said...

It is working however the status returned to Unix is limited to a single 1 number (in the range of 0..255).

If you run a shell script like this:

#!/bin/csh -v


sqlplus -s / <<EOF
whenever sqlerror exit SQL.SQLCODE
declare
e exception;
pragma exception_init( e, -1652 );
begin
raise e;
end;
/
EOF

echo $status from SQLPlus



sqlplus -s / <<EOF
whenever sqlerror exit SQL.SQLCODE
declare
e exception;
pragma exception_init( e, -254 );
begin
raise e;
end;
/
EOF

echo $status from SQLPlus

sqlplus -s / <<EOF
whenever sqlerror exit SQL.SQLCODE
declare
e exception;
pragma exception_init( e, -255 );
begin
raise e;
end;
/
EOF

echo $status from SQLPlus

sqlplus -s / <<EOF
whenever sqlerror exit SQL.SQLCODE
declare
e exception;
pragma exception_init( e, -256 );
begin
raise e;
end;
/
EOF

echo $status from SQLPlus


-------------------------------------------------------------------

you will recieve

$ test.csh
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by in tablespace
ORA-06512: at line 5


116 from SQLPlus

declare
*
ERROR at line 1:
ORA-00254: error in archive control string ''
ORA-06512: at line 5


254 from SQLPlus
declare
*
ERROR at line 1:
ORA-00255: error archiving log of thread , sequence #
ORA-06512: at line 5


255 from SQLPlus
declare
*
ERROR at line 1:
ORA-00256: cannot translate archive destination string
ORA-06512: at line 5


0 from SQLPlus



There is nothing we can do about this -- you might use GREP to GREP the ORA- messages out and just use:

whenever sqlerror exit 1

so you can detect an error.



Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 04, 2002 - 11:01 pm UTC

Tom,

We regularly use "grep ORA- > err.log" on the log files to trap the error messages. Do you know how we can trap the preceding 2 lines of the line that has ORA- error message also in the error log.

Eg:

line1
line2
line3
line4 ORA-xxxx
line5

I want to trap

line2
line3
line4 ORA-xxxx

in the error log. Is it possible? Thanks.

Tom Kyte
September 05, 2002 - 7:30 am UTC

write an awk script would be one way.

Oracle error to Unix

Philip, February 09, 2004 - 10:06 am UTC


I am trying to call a procedure from a unix script through sqlplus and I would like to stop the processing of unix script if any errors occur in the procedure. I am not able to pass the error code from my procedure to Unix. I use WHENEVER SQLERROR EXIT SQL.SQLCODE before executing the procedure and tried to check the status(echo $status), but it shows null.

Could you help me to find out what's wrong?


Tom Kyte
February 09, 2004 - 10:22 am UTC

might you be using sh or some other scripting environment?  (i used csh, it'll vary)

for example:

[tkyte@tkyte-pc tkyte]$ cat t
#!/bin/sh
 
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
  raise program_error;
end;
/
exit
EOF<b>
echo 'Status = ' $?</b>
[tkyte@tkyte-pc tkyte]$ sh ./t
 
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 9 10:25:57 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA920PC> ops$tkyte@ORA920PC>   2    3    4  begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 2
 
 
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production<b>
Status =  101
</b>[tkyte@tkyte-pc tkyte]$


would be the sh way of doing it.  refer to your "man" page for your shell.


Also -- remember, exit codes are in the range 0..128 or 255 -- best to use

whenever sqlerror exit 1


(as noted above) instead of the sqlcode.  run this script to see why :)


#!/bin/sh
                                                                                                          
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
  raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
                                                                                                          
                                                                                                          
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
  raise_application_error( -20224, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
                                                                                                          
                                                                                                          
                                                                                                          
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
  raise_application_error( -20225, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?


 

very useful

Andrew, February 09, 2004 - 1:57 pm UTC


philip, February 10, 2004 - 10:43 am UTC


It seems very useful and a working solution, but some how it is not working for me. I am using sh(Bourne shell) and the only difference I can see is I am calling a database procedure through sqlplus instead of commands direct.

I have WHENEVER SQLERROR EXIT 1 before I call the procedure, but when I do echo $? after sqlplus I always get a value of 0 even if there is an error in the procedure.


Tom Kyte
February 10, 2004 - 3:32 pm UTC

how about "man sh" on your system to see what it says would be "right" for you.

or how about "you post your example" like I did mine and we can take a look see..

or how about you run my example "as is" to see if in fact it seems to work....

philip, February 11, 2004 - 1:49 pm UTC

I tried your example but it also give me a value 0 for the status. It seems nothing gets executed between the begin and end. I tried to run the script with some mispelled commands, it didn't give me an error. I tried to execute some valid SELECT statements, but I didn't see any results.

I think it just connect to Oracle and disconnect without executing any commands(or at lease I don't see any rsponse from Oracle). But the interesting thing is I am able to execute a database procedure from the script. No idea what's wrong and where?


Tom Kyte
February 11, 2004 - 2:47 pm UTC

your shell must be a totally "non standard shell" then.

this is really "basic" stuff -- works on solaris, works on linux, should work on pretty much any "standard" unix like OS.


If it is not working -- remove sqlplus from the equation all together and show us something that does work on your system. The problem is *not* sqlplus here at all -- but rather that we don't know how to write "sh" on your system (whatever it is)

philip, February 11, 2004 - 3:42 pm UTC

My Unix is HP-UX miladms B.11.11 U 9000/800 3255374999. The default shell is called POSIX which is similar to Korn shell and I don't have any problem in executing any unix script on this shell.

As I said in my previous response I am even able to run Oracle procedures by calling sqlplus from the shell script. So there is nothing wrong with the standard execution capability of the shell. The problem is sqlplus not returning the correct exit codes to Unix. It always return 0 no matter you have error in your procedure or not. It seems I need to setup something in the sqlplus environment to return the status properly? Following is the snippet of the script.

sqlplus -s test_721_d/tecsys@fnok_dms >>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec gl_export_hyperion('$EXPFILE','$EXPDIR')
set serveroutput off
set feed on veri on head on
exit
EOF
echo Status $?


Tom Kyte
February 11, 2004 - 5:18 pm UTC

do this,

compile this C program:

void main()
{
exit(55);
}


call it test.c and just

$ make test

then:


#!/bin/sh

./test
echo status = $?


and show us what that does. remove sqlplus from the equation here.

Watch your indentation

Alfonso, February 11, 2004 - 5:44 pm UTC

Indentation matters in Philip's code section.
This sample returns "Status = 255" (using ksh)

sqlplus / <<-EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?

But this sample returns "Status = 0" (note indented lines):
sqlplus / <<-EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?

Try Tom's examples verbatim and you shouldn't have a problem.
Regards.

Tom Kyte
February 11, 2004 - 6:54 pm UTC

the "/" not being in position 1 would be the cause.

there is no code being run.

Philip, February 12, 2004 - 9:08 am UTC


First let me answer to Tom.

I ran your C program and it gave me a status of 55 which I think what you are expecting.

To Alfonso

I ran both of your samples and both gave me a status of 255 and I don't think indentation matters. When you call a procedure from the script you don't need to use / at the end, Right? Please look at the code I listed in my previous response and let me know if anything wrong with that.

Tom Kyte
February 12, 2004 - 9:14 am UTC

I don't have a shelled called "posix", but in ksh, that EOF needs to be like this:

[tkyte@localhost tkyte]$ cat ./test.sh
#!/bin/ksh
export LOGFILE=foo.lst

sqlplus -s />>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec raise program_error;
set serveroutput off
set feed on veri on head on
exit
EOF

echo Status $?
[tkyte@localhost tkyte]$ ./test.sh
Status 1
[tkyte@localhost tkyte]$



"works for me"

don't know what else to say except try the SH or KSH -- using code provided above, as is -- verbaitim.


indentation does matter -- alot.

Unsolicited $ 0.02

Madhu, February 12, 2004 - 9:34 am UTC

Here is another approach we use to pass the exact Oracle error code from SQLPLUS to unix . We use HP-UX/ Korn Shell


RCODE=`sqlplus -s / << EOF

SET SHOWMODE off
SET ECHO off
SET FEEDBACK off
SET VERIFY off
SET RECSEP off
SET PAGES 0
SET TRIMSPOOL on
SET LINESIZE 2000
VARIABLE RCODE NUMBER ;


WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
DECLARE
BEGIN
:RCODE := rso_gen_email_info('${EMAIL_FILE}');
END;
/

print :RCODE

exit :RCODE

EOF`

echo $RCODE


This will print the exact Oracle return code . As Tom mentioned in previous post, indentation matters a lot .

Thanks,
Madhu



Philip, February 12, 2004 - 10:34 am UTC


Sorry guys, I wasn't capturing the error in my procedure correctly. I was under the impression that oracle will return the error even if I didn't raise_application_error for oracle defined exceptions. I was just using dbms_ouput to capture the error.

There was nothing wrong with the shell or the useage of the script, all because of the procedure. I think you will excuse since I am still learning pl-sql programming.

Thanks any way for the great help you all guys provided in this regard.


Significance of Indentation for UNIX Inline Input Redirection

Dave Kramer, Oracle APPS DBA, SBC Services, Inc, May 22, 2004 - 10:12 am UTC

The content of the inline input can be safely indented using spaces or tabs provided:

1) a hyphen is used to prefix the end of file string
2) the end of file string appears anywhere on a line by itself -- it can be indented using TAB characters.

Building on the prev example in the thread:

$ cat -vt demo4.sh
#!/bin/ksh
export LOGFILE=foo.lst

sqlplus -s />>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec raise program_error;
set serveroutput off
set feed on veri on head on
exit
^IEOF

echo Status $?


$ ksh demo3
Status 1
$ cat foo.lst
BEGIN raise program_error; END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 1


$

Alternative tests:
1) remove the tab --> success;
2) replace the tab with 1 to many spaces --> fails to execute sqlplus.

Dealing with UNIX far more than SQL*Plus, I have always used tabs for all indentation.

how that Unix RC is determined?

david s, January 03, 2005 - 12:35 pm UTC

SQL> select 'a'+1 from dual
       *
ERROR at line 1:
ORA-01722: invalid number

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and OLAP options
JServer Release 9.2.0.5.0 - Production
/home>echo $?
186

Might it be true that the Oracle code "1722" can be modded with 256 to generate 186?  This worked also with 20223 code going to 255.  Only 2 examples, but does show promise... 

Tom Kyte
January 03, 2005 - 10:22 pm UTC

yes, but don't forget -- 1 out of every 256 error codes mod to 186 as well!!!

I'd stick to a 'return 1' for an error

Gary, January 03, 2005 - 11:19 pm UTC

"1 out of every 256 error codes mod to 186 as well!!! "

and, 1 out of every 256 will mod to zero too. If you use a return code of zero to indicate success, you may miss errors including :

ORA-01536: space quota exceeded for tablespace ....
ORA-12800: system appears too busy for parallel query execution


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library