DML Error Logging - DBMS_ERRLOG.CREATE_ERROR_LOG

DML Error Logging is a new feature of Oracle 10g R2. You must have tried an insert-into-select statement that got aborted after 30 minutes because one column value is too large? With DML error logging, we can have all correct records inserted successfully in our table, and bad records to be written in log table to resolve the issue later.

The CREATE_ERROR_LOG procedure of DBMS_ERRLOG package makes you enables to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back.

Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_errlog.htm

Following is the syntax of DBMS_ERRLOG.CREATE_ERROR_LOG

DBMS_ERRLOG.CREATE_ERROR_LOG
(
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2
)

If "err_log_table_name" is not specified that the log table will be created with first 25 characters of dml_table_name table prefixed with 'ERR$_'

Lets say we have following table, and we wanto to log the DML error on it:

SQL> CREATE TABLE EMPLOYEE
  2  (
  3     EMPID NUMBER(10),
  4     ENAME VARCHAR2(100),
  5     DEPTNAME VARCHAR2(100),
  6     SALARY NUMBER(10) CHECK (SALARY>=1000 AND SALARY<=5000)
  7  );

Table created.

SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('employee');

PL/SQL procedure successfully completed.

SQL> desc ERR$_EMPLOYEE;
 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)
 EMPID                                            VARCHAR2(4000)
 ENAME                                            VARCHAR2(4000)
 DEPTNAME                                         VARCHAR2(4000)
 SALARY                                           VARCHAR2(4000)

Here ERR$_EMPLOYEE table has been created by DBMS_ERRLOG.CREATE_ERROR_LOG, to record DML Error logs of EMPLOYEE table.

Lets say if some-one try to insert following command,

SQL> INSERT INTO EMPLOYEE
  2  SELECT EMPNO, ENAME, DNAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D
  3  WHERE E.DEPTNO=D.DEPTNO
  4  LOG ERRORS INTO ERR$_EMPLOYEE REJECT LIMIT UNLIMITED;

12 rows created.

It will insert 12 rows out of 14 of SCOTT.EMP table, 2 got rejected due to the check constraint, which can be review after the command execution as:

SQL> COL ORA_ERR_MESG$ FOR A50
SQL> COL ENAME FOR A25
SQL> COL SALARY FOR A10
SQL> SELECT ORA_ERR_MESG$, ENAME, SALARY FROM ERR$_EMPLOYEE;

ORA_ERR_MESG$                                      ENAME                SALARY
-------------------------------------------------- -------------------- ----------
ORA-02290: check constraint (NIMISH.SYS_C0030353)  SMITH                800
violated

ORA-02290: check constraint (NIMISH.SYS_C0030353)  JAMES                950
violated

DML Error Logging Handles following exceptions:

• Too-large column values
• Constraint violations
• Trigger execution errors
• Type conversion errors
• Partition mapping errors