J’ai eu besoin récemment d’identifier sur un traitement d’insertion dans une table partitionnée quelles étaient les lignes qui n’avaient pas pu être insérées faute de partition correspondante. Il existe différents moyens d’identifier ces lignes, je vous propose ici d’utiliser le package DBMS_ERRLOG .
Ce package n’est pas une nouveauté, il est arrivé avec la version 10gR2 et permet de capturer les lignes en exception sur des erreurs DML provoquées par des:

• Valeurs trop larges pour la colonne
• Violation de contrainte
• Erreur d’exécution de trigger
• Erreur de conversion de type
• Erreur d’identification de partition

Les ordres DML supportés sont : INSERT, UPDATE, DELETE et MERGE
On peut utiliser DBMS_ERRLOG  pour identifier les lignes mais aussi grâce à clause REJECT LIMIT UNLIMITED pour permettre artificiellement à un traitement de s’exécuter complètement sans générer d’erreur.

Voici un petit exemple pour illustrer l’utilisation d’ERRLOG .

Tout d’abord,créons une table T partitionné par RANGE et ayant contrainte « check » p sur la colonne SALARY  pour illustrer quelques possibilités de détection d’erreur du package ERRLOG.

CREATE TABLE T
    (
       EMPID NUMBER(10),
       ENAME VARCHAR2(50),
       SALARY NUMBER(10) CHECK (SALARY>=10 AND SALARY<=1000)
    ) TABLESPACE TOOLS
PARTITION BY RANGE(EMPID)
( PARTITION P1 VALUES LESS THAN (2) ,
 PARTITION P2 VALUES LESS THAN (5)  ,
 PARTITION P4 VALUES LESS THAN (10)  );

Il faut maintenant créer la table de log des exceptions pour cette table T, en utilisant la procédure CREATE_ERROR_LOG fournie par le package.

BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('T');
END;
/

Ceci crée une table nommée  ERR$_T dans le schéma courant, cette table aura la structure suivante, composée des colonnes spécifiques suivantes ainsi que des colonnes de la table T :

ORA_ERR_NUMBER$   NUMBER
ORA_ERR_MESG$     VARCHAR2(2000)
ORA_ERR_ROWID$    UROWID(4000)
ORA_ERR_OPTYP$    VARCHAR2(2)
ORA_ERR_TAG$      VARCHAR2(2000)
EMPID NUMBER(10)
ENAME VARCHAR2(50)
SALARY NUMBER(10)

Restrictions

Des restrictions existent sur le type des colonnes de la table à tracer, celles-ci ne peuvent être aucun des types suivants : LONG, BLOB, CLOB, BFILE et ADT.
Si la table à tracer contient un de ces types, il est possible de les ignorer en spécifiant skip_unsupported=TRUE dans la procédure de création de la table:

DBMS_ERRLOG.CREATE_ERROR_LOG('T',skip_unsupported=>TRUE);

Utilisation

L’utilisation la plus simple de l’errorlog se fait en ajoutant la directive LOG ERRORS REJECT LIMIT x à la suite de l’ordre DML selon la syntaxe suivante :

ordre DML + LOG ERRORS [INTO [schema.]table] [('expression_simple')] [REJECT LIMIT integer|UNLIMITED]

• ‘expression_simple’ : correspond à un TAG que l’on retrouvera dans la colonne    qui permet de poser une identification sur l’opération ou  le traitement en cours ayant généré l’exception dans la table d’errorlog.
• LOG ERRORS : les lignes rejetées seront insérées par défaut dans la table ERR$_<Table> ou sinon dans la table spécifiée par INTO. En effet, Oracle permet de créer une table de log personnalisée afin de ne tracer que certaines colonnes . Il suffit pour cela de créer une table classique incluant les 5 colonnes spécifiques
• REJET LIMIT : permet de spécifier se seuil au delà duquel l’ordre DML entier est annulé comme dans l’utilisation sans errorlog. La valeur par défaut de 0 conduit au fonctionnement classique qui annule le traitement dans son entier à la 1ère erreur. La valeur UNLIMITED permet de logger toutes les exceptions sans provoquer d’erreur au niveau de l’ordre DML

Par exemple :

 UPDATE T SET sal = sal * 2 LOG ERRORS REJECT LIMIT 5;

L’update de T supportera au maximum 5 erreurs avant d’échouer, les lignes en exception seront capturées dans la table ERR$_T.

Autre exemple d’utilisation

De nombreux exemples d’utilisation existent dans la littérature, l’exemple ci-dessous permet d’illustrer 3 types d’exceptions capturées par l’errlog:

•  Partition invalide
• Valeur insérée trop large pour la colonne VARCHAR2(50)
• Violation de contrainte

 set serveroutput on
 declare
    str varchar2(50);
    sal number := 10;
        rows integer := 0;
         begin
             for i in 1 .. 13
             loop
             str := str || 'AA';
             sal := sal * i ;              
               insert into t values(i,str,sal) LOG ERRORS INTO ERR$_T ('Insert T ') REJECT LIMIT 1;
              rows := rows + sql%rowcount;      
             end loop;             
             dbms_output.put_line (rows || '  rows inserted');
             commit;
        end;
 /
4 total rows inserted

4 lignes sur 13 ont été insérées dans T , les autres loggées dans ERR$_T

SQL> select * from T;
     EMPID ENAME                                                  SALARY
---------- -------------------------------------------------- ----------
         1 AA                                                         10
         2 AAAA                                                       20
         3 AAAAAA                                                     60
         4 AAAAAAAA                                                  240

Les de ERR$_T donnent la nature de l’erreur et la valeur des colonnes :

SELECT ora_err_number$,SUBSTR(ora_err_mesg$,1,100) err_msg            , empid,ename,salary,ORA_ERR_TAG$     FROM err$_t;

ORA_ERR_NUMBER$
---------------
ERR_MSG
--------------------------------------------------------------------------------
EMPID
--------------------------------------------------------------------------------
ENAME
--------------------------------------------------------------------------------
SALARY
--------------------------------------------------------------------------------
ORA_ERR_TAG$
--------------------------------------------------------------------------------
           2290
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
5
AAAAAAAAAA
1200
Insert T
           2290
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
6
AAAAAAAAAAAA
7200
Insert T
           2290
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
7
AAAAAAAAAAAAAA
50400
Insert T
           2290
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
8
AAAAAAAAAAAAAAAA
403200
Insert T
           2290
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
9
AAAAAAAAAAAAAAAAAA
3628800
Insert T
          14400
ORA-14400: inserted partition key does not map to any partition
10
AAAAAAAAAAAAAAAAAAAA
36288000
Insert T
          14400
ORA-14400: inserted partition key does not map to any partition
11
AAAAAAAAAAAAAAAAAAAAAA
399168000
Insert T
          14400
ORA-14400: inserted partition key does not map to any partition
12
AAAAAAAAAAAAAAAAAAAAAAAA
4790016000
Insert T
           1438
ORA-01438: value larger than specified precision allowed for this column
13
AAAAAAAAAAAAAAAAAAAAAAAAAA
62270208000
Insert T

3 types d’erreurs différentes ont été capturés, il ne reste plus qu’à corriger ou ignorer tout (LIMIT UNLIMITED) si on veut que le traitement aboutisse dans tous les cas.
On peut voir que le tag « Insert T’ a été inséré dans la colonne ORA_ERR_TAG$ ce qui peut permettre de rechercher des lignes d’un traitement particulier. Il y est possible d’ajouter d’informations dynamiques par concaténation par exemple LOG ERRORS INTO ERR$_T (‘Insert T- Valeur =>’||i) …
Si on utilise REJECT LIMIT 0, la 1ère erreur fait échouer globalement tout le bloc PL/SQL provoquant la remontée de l’erreur suivante et aucune ligne insérée dans la table T. La ligne ayant généré l’erreur est loggée dans ERR$_T :

ERROR at line 1:
ORA-02290: check constraint (LPTDWH_ODSTEST2.SYS_C0044249) violated
ORA-06512: at line 10
SQL> select count(*) from t;
  COUNT(*)
----------
         0
SQL> select count(*) from err$_t;
  COUNT(*)
----------
         1

Si vous n’avez plus besoin du logging , il suffit de dropper la table ERR$_T et bien sûr si nécessaire d’enlever les clauses LOG ERRORS …

drop table err$_t;