Logging in Error-Tabelle

Dieser Tage wurde ich an die Möglichkeit erinnert, Fehler bei DML-Operationen in einer DML-Error-Logging-Tabelle zu protokollieren, was ich mal wieder als Option betrachte, an PL/SQL vorbei zu kommen...

Der Database Data Warehousing Guide erläutert diese Option grundsätzlich folgendermaßen:

DML error logging extends existing DML functionality by enabling you to specify the name of an error logging table into which Oracle Database should record errors encountered during DML operations. This enables you to complete the DML operation in spite of any errors, and to take corrective action on the erroneous rows at a later time.

This DML error logging table consists of several mandatory control columns and a set of user-defined columns that represent either all or a subset of the columns of the target table of the DML operation using a data type that is capable of storing potential errors for the target column.

Ein detailliertes Beispiel für das Vorgehen gibt's bei Tim Hall; für den eiligen Nutzer hier eine kleinere Demonstration:

-- Anlage einer Tabelle TEST
create table test
( id number
, name varchar2(10)
, description varchar2(32)
, some_data number(8));

-- Anlage einer passenden ERROR-Table zur Tabelle TEST
-- mit Hilfe des dbms_errorlog-Packages
exec dbms_errlog.create_error_log (dml_table_name => 'test');

-- Die erzeugte ERROR-Tabelle besitzt das Präfix ERR$_
-- und enthält alle Spalten der Zieltabelle als VARCHAR2(4000)
-- und zusätzlich diverse Spalten zur Speicherung von Fehler-
-- Informationen
desc ERR$_TEST

Name                                      Null?    Typ
----------------------------------------- -------- -------------------
ORA_ERR_NUMBER$                                    NUMBER
ORA_ERR_MESG$                                      VARCHAR2(2000)
ORA_ERR_ROWID$                                     ROWID
ORA_ERR_OPTYP$                                     VARCHAR2(2)
ORA_ERR_TAG$                                       VARCHAR2(2000)
ID                                                 VARCHAR2(4000)
NAME                                               VARCHAR2(4000)
DESCRIPTION                                        VARCHAR2(4000)
SOME_DATA                                          VARCHAR2(4000)

Details zum Format der ERROR-Tabelle liefert der Administrator's Guide:

• ORA_ERR_NUMBER$: Oracle error number
• ORA_ERR_MESG$: Oracle error message text
• ORA_ERR_ROWID$: Rowid of the row in error (for update and delete)
• ORA_ERR_OPTYP$: Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
• ORA_ERR_TAG$: Value of the tag supplied by the user in the error logging clause

Mit Hilfe der Klausel LOG ERRORS INTO ... kann man nun eine DML-Operation zum erfolgreichen Abschluss bringen, die sonst auf Fehler gelaufen wäre:

-- mein Insert klappt erst mal nicht
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000;

     , rownum * 1000000
              *
FEHLER in Zeile 5:
ORA-01438: Wert größer als die angegebene Gesamststellenzahl, die für diese Spalte zulässig ist

-- aber mit der LOG ERRORS INTO Klausel geht's durch
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000
log errors into err$_test ('insert') reject limit unlimited;

99 Zeilen wurden erstellt.
--> die übrigen 9900 Sätze landen dabei in ERR$_TEST

Offenbar wird dabei nur der erste auftretende Fehler protokolliert:

-- Löschung der Daten der ERROR-Tabelle:
truncate table err$_test;

-- dazu ein INSERT, das für mehrere Spalten unverdauliche Daten enthält:
insert into test 
values ( 1
       , 'blaaaaaaaaaaaaaaaaaaaaaa'
       , 'ssssssssssssssssssssssssssspppppppppppppppppppaaaaaaaaaaaaaaaaaaaaaaaaammmmmmmmmmmmmmmmm'
       , 123456789000000000000)
log errors into err$_test ('insert') reject limit unlimited;

0 Zeilen wurden erstellt.

--> was für die Spalten 2 - 4 nicht klappen kann

-- Die Metadaten zum Fehler sind dann:

select ora_err_number$
     , ora_err_mesg$
     , ora_err_rowid$
     , ora_err_optyp$
     , ora_err_tag$
  from err$_test;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                  ORA_ERR_ROWID$  ORA_ERR_OPTYP$  ORA_ERR_TAG$
--------------- ------------------------------ --------------- --------------- ---------------
          12899 ORA-12899: Wert zu groß für Sp                 I               insert
                alte "DBADMIN"."TEST"."NAME" (
                aktuell: 24, maximal: 10)

-- dann folgen in err$_test die Werte des gescheiterten INSERTs.

Die Dokumentation (in diesem Fall die SQL Language Reference) nennt noch folgende Fälle, in denen der Mechanismus nicht verwendbar ist - also ein Fehler auftritt und ein Rollback durchgeführt wird:

• Verletzung von deferred constraints.
• Direct-Path-Operationen, die eine unique constraint oder index violation hervorrufen.
• Update oder Merge Operationen, die eine unique constraint oder index violation hervorrufen.

Für LONG, LOB und Objekt-Typen gelten spezielle Bedingungen, deren Aufzählung ich mir hier aber mit Verweis auf die Doku spare.