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:
This is really useful information, but the design of your blog and use of colors prevents a viewer from reading it.
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
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.
Okay I managed to read it once it was safely inside notepad++.
Good stuff, thanks.
Colors changed, flame off.
Thanks for your feedback.
Flavio
Post a Comment