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.

ORA-12640 aka TNS-12640

I recently installed patch 10.1.0.5 on my development box, a windows 2000 box.
When i install oracle software i always log on with the OS administrator account, but when i am developing applications, i normally log on as a power user because i don't need administrator privileges.
For some reason that i still have to investigate, after patching the db, i could no longer log on to the db from the command line using something like:

sqlplus /nolog

connect / as sysdba

because the following error was returned:

ORA-12640: Authentication adapter initialization failed

After a few attempts, it turned out that if i added the os user to the administrators group, the same log on procedure would succeed.
To be more precise i had also to sign off and on again in order to acknowledge the change of permissions.

This behaviour seems to be related to the protocol that oracle uses to communicate with the db when the client and the server are located on the same box.
Indeed it is remarkable to note that if you try to log on in this way:

sqlplus "usr/pwd@dbconn as sysdba"

you'll get the same ORA-12640 error if you do it on the server, while it would succeed on a remote client.

And the same happens when running Golden32.exe or PLEdit.exe , two nifty utilities from Benthic Software.

---

ORA-12640: Inizializzazione adattatore di autenticazione non riuscita
ORA-12640: Fallo en la inicialización del adaptador de autenticación
ORA-12640: No s'ha pogut inicialitzar l'adaptador d'autenticació
ORA-12640: Initialisation impossible de l'adaptateur d'authentification
ORA-12640: Inititalisierung des Berechtigungsprüfungsadapters misslungen
ORA-12640: Αρχικοποίηση οδηγού προσαρμογής ελέγχου ταυτότητας απέτυχε
ORA-12640: Initialisering af autentificeringsadapter fejlede
ORA-12640: Fel vid initiering av adapter för autentisering
ORA-12640: Initialisering av autentiseringsadapter mislyktes
ORA-12640: Valtuustarkistussovittimen alustus epäonnistui
ORA-12640: Az igazolási szolgáltatás csatolójának inicializálása sikertelen
ORA-12640: Iniţializarea adaptorului pentru autentificare a eşuat
ORA-12640: Initialiseren van verificatieadapter is mislukt.
ORA-12640: Falha ao inicializar o adaptador de autenticação
ORA-12640: Falha na inicialização do adaptador de autenticação
ORA-12640: Сбой при инициализации адаптера аутентификации
ORA-12640: Selhala inicializace ověřovacího adaptéru
ORA-12640: Iniciácia autentifikačného adaptéra zlyhala
ORA-12640: Niepowodzenie inicjalizacji adaptera identyfikacji
ORA-12640: Yetki doğrulama adaptörü başlatılamadı