Oracle : SET ERRORLOGGING et table de trace des erreurs SQL

Avec Oracle 11g il est possible de tracer dans une table (par défaut nommée SPERRORLOG) les erreurs SQL d'un traitement.


Créez une table avec ce format (en cas d'absence Oracle tentera de la créer automatiquement) :

CREATE TABLE HR.SPERRORLOG (
USERNAME  VARCHAR(256) ,  -- Oracle account name.
TIMESTAMP TIMESTAMP    ,  -- Time when the error occurred.
SCRIPT    VARCHAR(1024), -- Name of the originating script if applicable.
IDENTIFIER VARCHAR(256), -- User defined identifier string.
MESSAGE    CLOB        , -- ORA, PLA or SP2 error message.
STATEMENT  CLOB          -- The statement causing the error.
) TABLESPACE USERS;

Pour activer la trace :
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]

L'option TABLE permet de spécifier une autre table que celle par défaut, TRUNCATE nettoie la table en début de transaction, IDENTIFIER permet de spécifier un identifiant pour les enregistrements créés.

Exemple avec un DML :
SET ERRORLOGGING ON IDENTIFIER MY_SQL_ACTIONS

SQL> insert into AP15 values ('EX','FDCAL')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.IXAP15) violated

SQL> select * from SPERRORLOG;
--------------------------------------------------------------------------------
HR
12-SEP-14 01.32.48.000000 PM

MY_SQL_ACTION
ORA-00001: unique constraint (HR.IXAP15) violated    
insert into AP15 values ("EX","FDCAL")

C'est effectif aussi avec un DDL.
Ci dessous suite à une création de table en erreur, et avec utilisation d'un script :

SQL>@DDL_ZL10.sql
...
SQL> select * from SPERRORLOG;
--------------------------------------------------------------------------------
HR
12-SEP-14 01.57.57.000000 PM
DDL_ZL10.sql

ORA-00955: name is already used by an existing object
CREATE TABLE       HR.ZL10
 (      NUDOSS       NUMBER(38)                        NOT NULL ,
        SOCDOS       CHAR                    (0003)    NOT NULL ,
  ...

A noter : cet automatisme fonctionne un peu comme un "trigger" et :

• Pour les DML, en cas de ROLLBACK la table de trace SPERRORLOG revient à sa situation initiale,
• en cas d'option "WHENEVER SQLERROR EXIT" le traitement est interrompu avant d'être tracé dans la table.