Introduction
Cet article est le premier d'une série consacrée à la gestion des erreurs sous Oracle. Ce sujet est vaste, ces erreurs pouvant être gérées sous SQL*Plus, en PL/SQL, dans des fichiers, via des triggers, j'en passe et des meilleurs :-)  Attention, le but ici est de présenter cette gestion des erreurs, pas d'en faire une analyse poussée et exhaustive, ça, c'est à vous de le faire selon vos besoins...

Les autres articles sont ici : 
les erreurs sous oracle 2 : contraintes d'intégrité, whenever oserror/sqlerror, trigger servererror, fichiers alert.log/trace

Aujourd'hui nous allons regarder quatre choses : 
- les erreurs syntaxiques et sémantiques
- l'utilitaire Linux OERR
- tracer les erreurs SQL : la table SPERRORLOG
- la tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT
 


 

Points d'attention
Aucun.
 


 
Base de tests
Une base Oracle 12.


 
Exemples
============================================================================================
Les erreurs syntaxiques et sémantiques

============================================================================================
Il existe deux grand types d'erreur sous Oracle, les erreurs syntaxiques et les erreurs sémantiques. Malheureusement il n'est pas possible de les distinguer via un code! Si le besoin existe, alors il faut lister les erreurs les plus fréquentes des deux types et faire ensuite une gestion numéro d'erreur par numéro d'erreur; dans un programme PL/SQL par exemple.

Erreur syntaxique : c'est quand un ordre SQL est mal écrit. Ici il n'y a aucun nom de colonne ni le caractère *. 
     SQL> SELECT FROM test444;
     SELECT FROM test444
     *
     ERROR at line 1:
     ORA-00936: missing expression

Exemple d'erreur sémantique : la syntaxe est bonne mais il y a un pb avec les objets de la requête ou du user qui la lance; ici la table ou vue n'existe pas.
     SQL> select * from test444;
     select * from test444
     *
     ERROR at line 1:
     ORA-00942: table or view does not exist


============================================================================================
L'utilitaire Linux OERR

============================================================================================

Quand une erreur survient, un message est affiché par SQL*Plus.
     SQL> select * frommm;
     select * frommm
              *
     ERREUR a la ligne 1 :
     ORA-00923: mot-cle FROM absent a l'emplacement prevu
      
On peut parfois avoir plus d'information sur celle-ci en utilisant l'utilitaire Linux OERR; il a en effet deux champs appelés "Cause" et "Action" en plus. Cet outil extrait les messages d'erreur depuis un fichier Oracle; ce fichier peut-être mis à jour manuellement pour avoir des messages plus précis mais c'est hors scope de cet article.
     SQL> ! oerr
     Usage: oerr facility error
      
     Facility is identified by the prefix string in the error message.
     For example, if you get ORA-7300, "ora" is the facility and "7300"
     is the error.  So you should type "oerr ora 7300".
      
     If you get LCD-111, type "oerr lcd 111", and so on.
      
Parfois on n'a aucune info en plus de celle affichée par SQL*Plus; ah si, dans mon cas le texte est en anglais, ce qui en général est bien plus utile (avis personnel) :-)
     SQL> ! oerr ora 923
     00923, 00000, "FROM keyword not found where expected"
     // *Cause:
     // *Action:
      
Mais, parfois, selon le code erreur, les champs Cause et Action peuvent être renseignés et on a alors plus d'info que le message par défaut de SQL*Plus.
     SQL> ! oerr ora 42
     00042, 0000, "Unknown Service name %s"
     // *Cause:    An attempt was made to use an invalid application service.
     // *Action:   Use a valid service name from SERVICE$ or add a new
     //            service using the DBMS_SERVICE package.


============================================================================================
Tracer les erreurs SQL : la table SPERRORLOG

============================================================================================

Il est possible de tracer les erreurs survenues sous SQL*Plus dans une table dédiée, appelée SPERRORLOG. Les erreurs tracées sont les erreurs SQL mais aussi SQL*Plus. Au préalable, il faut activer cette fonctionnalité avec le paramètre errorlogging ("enregistrements des erreurs" en français). Cela a pour effet de créer une table SPERRORLOG dans le schéma courant.
     
Voici les options de la commande sous SQL*Plus.
     SQL> help set
     ...
     ERRORL[OGGING] {ON|OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]  
     ...
     
     SQL> set errorlogging on
     
     SQL> show user
     USER is "HR"
     
     SQL> select owner, object_type from dba_objects where object_name= 'SPERRORLOG';
     OWNER        OBJECT_TYPE
     -----------------------
     HR            TABLE
     
     SQL> desc SPERRORLOG
     Name                       Null?    Type
     ----------------------------------------- -------- ----------------------------
     USERNAME                        VARCHAR2(256)
     TIMESTAMP                        TIMESTAMP(6)
     SCRIPT                             CLOB
     IDENTIFIER                        VARCHAR2(256)
     MESSAGE                            CLOB
     STATEMENT                        CLOB
     
On va tester avec deux types d'erreur.
Erreur SQL : SELECT qui provoque une erreur.
     SQL> select * from test99;
     select * from test99
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
Erreur SQL*Plus.
     SQL> col STATEMENT col A30
     SP2-0158: unknown COLUMN option "col"

OK, les messages d'erreurs sont bien sauvegardés dans la nouvelle table.
     SQL> SELECT * from SPERRORLOG order by TIMESTAMP;
     USERNAME       TIMESTAMP          SCRIPT    IDENTIFIER MESSAGE                     STATEMENT
     ---------- ------------------------------ ------------------- ---------- ----------------------
     HR     08-AUG-19 01.26.14.000000 PM   ORA-00942: table or view does not exist      select * from test99
     HR     08-AUG-19 01.29.29.000000 PM   SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
A noter que même si je ferme ma session, le contenu de la table SPERRORLOG n'est pas supprimé; ouf, cela permet d'avoir un historique des erreurs :-)
     SQL> exit
     Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
     
     [oracle@vbgeneric ~]$ sqlplus HR
     Enter password:
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ---------------------------------------- ----------------------------------------
     08-AUG-19 01.26.14.000000 PM       ORA-00942: table or view does not exist        select * from test99
     08-AUG-19 01.29.29.000000 PM       SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
On peut aussi créer sa propre table d'erreurs, pour éviter que la table SPERRORLOG ne soit purgée, par un batch de DBA par exemple.
     SQL> create table my_sperror_log
         (
            username varchar2(256),
            timestamp       timestamp,
            script          clob,
            identifier      varchar(256),
            message         clob,
            statement       clob
         );
     Table created.
     
Avec l'option "errorlogging on table", je donne le nom d'une table pour tracer les erreurs et on voit bien que SPERRORLOG n'est plus mise à jour.
     SQL> set errorlogging on table my_sperror_log;
     
     SQL> select * from test 55555;
     select * from test 55555
                        *
     ERROR at line 1:
     ORA-00933: SQL command not properly ended
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from SPERRORLOG order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ---------------------------------------- ----------------------------------------
     08-AUG-19 01.26.14.000000 PM       ORA-00942: table or view does not exist        select * from test99
     08-AUG-19 01.29.29.000000 PM       SP2-0158: unknown COLUMN option "col"        col STATEMENT col A30
     
     SQL> select TIMESTAMP, MESSAGE, STATEMENT from my_sperror_log order by TIMESTAMP;
     TIMESTAMP                           MESSAGE                                        STATEMENT
     ------------------------------ ----------------------------------------     -------------------------
     08-AUG-19 01.40.06.000000 PM       ORA-00933: SQL command not properly ended     select * from test 55555
    
Si on vide la table des erreurs sans préciser un nom, c'est la tablecourante qui est vidée et pas forcément SPERRORLOG.
     SQL> set errorlogging on truncate
     
     SQL> select count(*) from SPERRORLOG;         
       COUNT(*)
     ----------
          2
     
     SQL> select count(*) from my_sperror_log;
       COUNT(*)
     ----------
          0
     
L'option IDENTIFIER quant à elle permet d'attribuer un id à chaque session dans la table des erreurs.Ainsi on pourra filtrer les erreurs sur un user. Attention, pas de quotes ou guillemets pour le libellé de l'identifiant.
     SQL> set errorlogging on identifier DAVID;
     SQL>
     
     SQL> SELECT * FROM cvfg;
     SELECT * FROM cvfg
                   *
     ERROR at line 1:
     ORA-00942: table or view does not exist
     
     SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from my_sperror_log where identifier = 'DAVID' order by TIMESTAMP;
     TIMESTAMP                      IDENTIFIER    MESSAGE                                   STATEMENT
     ------------------------------ -------------------- ------------------------------------------------------
     08-AUG-19 01.50.03.000000 PM   DAVID         ORA-00942: table or view does not exist   SELECT * FROM cvfg
     
J'ouvre maintenant une session avec le user SYS et je renouvelle l'expérience, en utilisant bien comme table des erreurs celle du user HR sinon Oracle va me créer dans le schéma SYS une table SPERRORLOG.
     SQL> connect SYS as sysdba
     SQL> set errorlogging on table HR.my_sperror_log;
     SQL> set errorlogging on identifier SYS;
     
Je provoque une erreur comme SYS. On voit bien ensuite qu'on peut filtrer les erreurs selon l'utilisateur qui a provoqué les erreurs. Il est alors facile de créer un "TRIGGER AFTER LOGON" et de renseigner pour chaque user connecté le paramètre IDENTIFIER avec le USERNAME de connexion. 
     SQL> set columnzzzz NAME for A30
     SP2-0158: unknown SET option "columnzzzz"
     
     SQL> select TIMESTAMP, IDENTIFIER, MESSAGE, STATEMENT from HR.my_sperror_log order by TIMESTAMP;
     TIMESTAMP                      IDENTIFIER   MESSAGE                                         STATEMENT
     ------------------------------ -------------------- ---------------------------------------- -------------------
     08-AUG-19 01.50.03.000000 PM   DAVID        ORA-00942: table or view does not exist      SELECT * FROM cvfg
     08-AUG-19 01.55.54.000000 PM   SYS          SP2-0158: unknown SET option "columnzzzz"      set columnzzzz NAME for A30


============================================================================================
La tolérance aux erreurs DML : package DBMS_ERRLOG, table ERRLOG et clause LIMIT

============================================================================================

Dans la syntaxe du INSERT, UPDATE, DELETE, MERGE depuis Oracle 10g, il est possible de logger les erreurs des opérations DML de façon détaillées (avec le code et le message Oracle) et même de bypasser les erreurs pour éviter qu'un programme ne s'arrête et n'annule tout ce qui s'est bien déroulé avant.

Création de la table de tests avec 100 enregistrements.
     SQL> CREATE TABLE ZZZ_DDU(ID NUMBER(7) NOT NULL, LIB VARCHAR2(20 CHAR));
      
     SQL> BEGIN
               FOR i in 1..100
                    LOOP
                         INSERT INTO ZZZ_DDU VALUES(i, TO_CHAR(i));
               END LOOP;
               COMMIT;
     END;
     /
      
Création de la table stockant les erreurs avec le package DBMS_ERRLOG pour la table ZZZ_DDU.
Voici la définition de ce package dans la doc Oracle; à noter qu'il ne contient qu'une seule procédure.
"The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources."
     SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ZZZ_DDU', 'errlog');
      
Test avec N Insert qui plantent mais sans la clause REJECT LIMIT : seule la première erreur est tracée car le programme s'arrête de suite.
     SQL> BEGIN
               FOR i in 1000..1010
               LOOP
                    INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU');
               END LOOP;
               COMMIT;
     END;
     /
      
     BEGIN
     *
     ERREUR a la ligne 1 :
     ORA-12899: valeur trop grande pour la colonne "ZZTEST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20)
     ORA-06512: a ligne 4
      
     SQL> SELECT * FROM ERRLOG;
     ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_RO OR ORA_ERR_TAG$    ID    LIB
     --------------- -------------------------------------------------- ---------- -- --------------- -----
     12899 ORA-12899: valeur trop grande pour la colonne "ZZT  I  TEST ZZZ_DDU    1001  AXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     EST"."ZZZ_DDU"."LIB" (reelle : 50, maximum : 20)                                       XXXXXXXXXXXXXXXXXXXX
      
Test avec 11 INSERTs qui plantent avec la clause REJECT LIMIT à 50 : toutes les erreurs sont bien logguées et Oracle continue à traiter les INSERTs tant qu'il n'arrive pas à 50 erreurs. On ne constate d'ailleurs aucun message d’erreur dans SQL*Plus sur cette opération.
     SQL> truncate table errlog;
     SQL> truncate table ZZZ_DDU;
     SQL> BEGIN
               FOR i in 1000..1010
               LOOP
                    INSERT INTO ZZZ_DDU VALUES(i, DECODE(MOD(i, 2), 0, to_char(i), RPAD('A', 50, 'X'))) LOG ERRORS INTO ERRLOG ('TEST ZZZ_DDU') REJECT LIMIT 50;
               END LOOP;
               COMMIT;
          END;
          /
    
Toutes les erreurs sont logguées dans la table des erreurs dans la limite du REJECT LIMIT.
     SQL> SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ID FROM ERRLOG order by ID;
     ORA_ERR_NUMBER$ ORA_ERR_TAG$    ID
     --------------- --------------- -----
               12899 TEST ZZZ_DDU    1001
               12899 TEST ZZZ_DDU    1003
               12899 TEST ZZZ_DDU    1005
               12899 TEST ZZZ_DDU    1007
               12899 TEST ZZZ_DDU    1009
     
Tous les INSERTsOK sont bien dans la table ZZZ_DDU, ils n’ont pas été annulés par le premier INSERT en erreur puisqu'on tolère 50 erreurs avant qu'un ROLLBACK ne soit déclenché.
     SQL> SELECT * FROM ZZZ_DDU order by ID;
             ID LIB
     ---------- ------------------------------
     ########## 1000
     ########## 1002
     ########## 1004
     ########## 1006
     ########## 1008
     ########## 1010
      
     6 lignes selectionnees.