Error logging using DBMS_ERRLOG

If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible.

DBMS_ERRLOG, a package introduced in Oracle10g Release 2, allows you to do precisely that. Here is a quick review of the way this package works. 

First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS.

1. Create an error log table for the table against which you will execute DML statements:

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP');
END;

Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it makes sense).

For example in our case, the EMP table has this structure:

EMPNO    NUMBER(4)
ENAME    VARCHAR2(10)
JOB      VARCHAR2(9)
MGR      NUMBER(4)
HIREDATE DATE
SAL      NUMBER(7,2)
COMM     NUMBER(7,2)
DEPTNO   NUMBER(2)

The ERR$_EMP table has this structure:

ORA_ERR_NUMBER$   NUMBER
ORA_ERR_MESG$     VARCHAR2(2000)
ORA_ERR_ROWID$    UROWID(4000)
ORA_ERR_OPTYP$    VARCHAR2(2)
ORA_ERR_TAG$      VARCHAR2(2000)
EMPNO             VARCHAR2(4000)
ENAME             VARCHAR2(4000)
JOB               VARCHAR2(4000)
MGR               VARCHAR2(4000)
HIREDATE          VARCHAR2(4000)
SAL               VARCHAR2(4000)
COMM              VARCHAR2(4000)
DEPTNO            VARCHAR2(4000)
 

From this we can understand that there are columns ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$ which will be created for extra information on the error encountered, the data at the time of the error etc.

2. Add the LOG ERRORS clause to your DML statement:

BEGIN
   UPDATE emp
      SET sal = sal * 2
      LOG ERRORS REJECT LIMIT UNLIMITED;
END;

3. After running your code, you can check the contents of the ERR$ table to see if any errors occurred. You can then perform row by row recovery, or transfer that error information to your application error log, etc.

This approach will not only allow you to continue past exceptions; it will also greatly improve performance of DML processing in which lots of exceptions would normally be raised, because exception handling is quite slow compared to writing a row to a log table via an autonomous transaction procedure.

Check out DBMS_ERRLOG. It could offer some very powerful alternatives ways of executing large numbers of DML statements, each of which might change many rows.

Conclusion
The disadvantage of this approach is in maintenance. The user is likely to report errors like "my data is not showing up, and no errors are shown". My advice on using this feature is to have a highly sophasticated error handling mechanism that handles such errors and throw messages to users. This way the debugging person will also have exact data as to what caused the error. Unless used responsibly this feature can be a disaster.