Wednesday, October 25, 2006

whenever sqlerror exit surprises

I am still unsure whether this is a quirk or not.
At any rate, it's useful information i guess.

Everything started while testing a batch script in a development environment running on Windows 2000, when i tried to replicate, with little success, the same scenario in the test environment, on a different box.

I started to scratch my head when the same script, against the same data, was not working the same as in my development environment.

To cut a long story short, it was all about o/s return codes, aka exit codes.

As you know in sqlplus there is an error trapping feature called whenever sqlerror exit [failure | warning | success | sql.sqlcode | ]

If you don't include it in the script, it's gonna be like writing
whenever sqlerror exit success, which could be a disaster in certain situations, because your sql script stops due to an error but you get return code 0 (zero) that normally indicates a normal end, so probably you won't notice anything wrong until your phone rings at an unusual time.

So, in order to test the various possibilities, i wrote these two short scripts:

testcode.sql:

whenever sqlerror exit &1
begin
raise_application_error(-20001,'testing exit code...'||'&1');
end;
/
and testcode.bat:
sqlplus %1 @testcode.sql %2
@echo exit code is %errorlevel%

usage and results are as follows:

> testcode test/test@dev10g warning

sqlplus test/test@dev10g @testcode.sql warning

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Oct 24 23:38:04 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'warning');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...warning
ORA-06512: at line 2


Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
exit code is 2
----------------------------------------------------------
>testcode test/test@dev10g failure

sqlplus test/test@dev10g @testcode.sql failure

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Oct 24 23:38:21 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'failure');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...failure
ORA-06512: at line 2


Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
exit code is 1
-----------------------------------------------------------
>testcode test/test@dev10g sql.sqlcode

sqlplus test/test@dev10g @testcode.sql sql.sqlcode

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Oct 24 23:38:47 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'sql.sqlcode');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...sql.sqlcode
ORA-06512: at line 2


Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
exit code is 20001
--------------------------------------------------------
>testcode test/test@dev10g 10

sqlplus test/test@dev10g @testcode.sql 10

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Oct 24 23:38:55 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'10');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...10
ORA-06512: at line 2


Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
exit code is 10

now, if we go back to version 8.1.7.0, the same tests return:

>testcode test/test@dev817 warning

sqlplus test/test@dev817 @testcode.sql warning

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Oct 25 00:53:13 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'warning');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...warning
ORA-06512: at line 2


Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
exit code is 2

>testcode test/test@dev817 failure

sqlplus test/test@dev817 @testcode.sql failure

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Oct 25 00:53:38 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'failure');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...failure
ORA-06512: at line 2


Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
exit code is 3

finally let's have a look at version 9.2 :
>testcode test/test@dev920 warning

sqlplus test/test@dev920 @testcode.sql warning

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Oct 25 01:09:10 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'warning');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...warning
ORA-06512: at line 2


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
exit code is 2

>testcode test/test@dev920 failure

sqlplus test/test@dev920 @testcode.sql failure

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Oct 25 01:09:19 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'failure');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...failure
ORA-06512: at line 2

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
exit code is 3

So, it seems that failure built-in exit code has been demoted starting from version 10.
It was 3 and now it's 1.
If you have an old script checking for %errorlevel% GTR 1 (or even GTR 2) and you are using whenever sqlerror exit failure, prepare for big surprises in case you migrate from version 8.1.7 (or ealier i guess) or version 9.

As far as i could see, this holds for both windows and solaris versions.
Not only, you can even get mixed results if you are using newer clients connecting to older databases. For instance a 10g client will return 1 as failure code even when connecting to a 9i database instance, as detailed below:
>testcode test/test@dev920 warning

sqlplus test/test@dev920 @testcode.sql warning

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Oct 25 01:24:45 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'warning');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...warning
ORA-06512: at line 2


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
exit code is 2

>testcode test/test@dev920 failure

sqlplus test/test@dev920 @testcode.sql failure

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Oct 25 01:24:33 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   2:   raise_application_error(-20001,'testing exit code...'||'&1');
new   2:   raise_application_error(-20001,'testing exit code...'||'failure');
begin
*
ERROR at line 1:
ORA-20001: testing exit code...failure
ORA-06512: at line 2


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
exit code is 1

A generic error checking test like %errorlevel% GTR 0 should work in most situations.
But for fine-grained error checking, you'll be better off using your own values, rather than relying on the built-ins.

Happy migration.

5 comments:

Anonymous said...

This is really useful information, but the design of your blog and use of colors prevents a viewer from reading it.

Byte64 said...

hum, ok, let me see if i can make it a little larger, i recently reduced the font size of PREformatted html text because some large strings were truncated, i will try another solution.

Thanks for you feedback.
Flavio

Unknown said...

Yes he's right. What's with all the (different) colours? Have you actually tried to read what you have posted? What's wrong with black on white, or failing that, white on black?
I'm going to have to copy and paste the whole lot into a code editor just to be able to read it.
Still, it looks like it might be both useful and interesting, so thanks for putting it up.

Unknown said...

Okay I managed to read it once it was safely inside notepad++.
Good stuff, thanks.

Byte64 said...

Colors changed, flame off.

Thanks for your feedback.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles