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.
