DML Error Logging

In Oracle 10gR2 wurde das Feature DML Error Logging eingeführt, mit dessen  Hilfe Werte von DML-Anweisungen protokolliert werden können, die bei der Ausführung der Anweisung Fehler verursachen. In einem aktuellen Projekt  konnte ein ETL-Prozess nicht fortfahren, da es Probleme mit der Aufnahme von Werten in eine Staging-Tabelle gab. Leider konnte anhand von Protokolldateien der fehlerhafte Datensatz  nicht ermittelt werden.  Somit wurde für die Staging-Tabelle das DML Error Logging aktiviert.


Veranschaulicht wird das DML Error Logging an der Tabelle TABERRTEST


SQL> CREATE TABLE TABERRTEST (S1 NUMBER, S2 VARCHAR2(10)) TABLESPACE USERS;

Table created.

SQL> ALTER TABLE TABERRTEST ADD CONSTRAINT PK_TABERRTEST PRIMARY KEY(S1);

Table altered.

In nächsten Schritt wird eine Fehlerprotokolltabelle mit dem Package DBMS_ERRLOG  und der darin enthaltenen Prozedur CREATE_ERROR_LOG erstellt.


SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TABERRTEST','ERRLOG_TABERRTEST') ;

PL/SQL procedure successfully completed.

SQL> DESCRIBE ERRLOG_TABERRTEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 S1                                                 VARCHAR2(4000)
 S2                                                 VARCHAR2(4000)

Diese Tabelle besitzt die Spalten ORA_ERR_NUMBER$ für die Fehlernummer, ORA_ERR_MESG$ für die Fehlermeldung, ORA_ERR_ROWID$ für die betroffene ROWID der Zeile beim Löschen oder Ändern, ORA_ERR_OPTYP$ für die DML-Operation und  ORA_ERR_TAG$ für benutzerdefinierte Zusatzinformationen, die mit der Klausel LOG ERRORS  INTO geschrieben werden können. Zusätzlich werden alle Spalten der Quelltabelle in der Fehlerprotokolltabelle mit aufgenommen.

Für die Protokollierung von Fehlern wird die Klausel LOG ERRORS INTO nun im nächsten Schritt in die DML-Anweisung verwendet.


SQL> INSERT INTO TABERRTEST VALUES(1,'Test1')
  2  LOG ERRORS INTO ERRLOG_TABERRTEST REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO TABERRTEST VALUES(1,'Test1')
  2  LOG ERRORS INTO ERRLOG_TABERRTEST REJECT LIMIT UNLIMITED;

0 rows created.

Die REJECT LIMIT Klausel bestimmt, nach wie viel abgelehnten Datensätzen die gesamte Anweisung fehlschlägt.


SQL> COL ORA_ERR_MESG$ FORMAT A30
SQL> COL ORA_ERR_ROWID$ FORMAT A10
SQL> COL S2 FORMAT A7
SQL> COL S1 FORMAT A3
SQL> SELECT * FROM ERRLOG_TABERRTEST;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                  ORA_ERR_RO OR ORA_ERR_TA S1  S2
--------------- ------------------------------ ---------- -- ---------- --- -------
              1 ORA-00001: unique constraint (            I             1   Test1
                HR.PK_TABERRTEST) violated

Selbst nach der Rückführung der Transaktion mit einem Rollback bleibt die Fehlerprotokolltabelle befüllt.