Oracle: log DML errors

When you perform a massive insert or update generating errors, it is very hard to recognize which are the records which are generating the errors.

The topic in this post is about performing a DML statement and storing the wrong error in an additional table rather than crashing the statement itself.


Suppose you run:

INSERT INTO dest
SELECT *
FROM   source;

SELECT *
       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


SQL>

But you would like:

INSERT INTO dest
SELECT *
FROM   source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

99998 rows created.

SQL>

How to do this?
First build the "Error table", which is a clone of your destination table, having no format for columns (every column is varchar).

LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

Example:

-- Create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
END;
/

PL/SQL procedure successfully completed.

SQL>

Which generate:

SQL> DESC err$_dest
 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)
 ID                                         VARCHAR2(4000)
 CODE                                       VARCHAR2(4000)
 DESCRIPTION                                VARCHAR2(4000)

SQL>

At this point you can invoke the insert in the way above.
Update and merge and code for a complete example are in the link below (source of this post).


Source:
http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php