DML Error Logging

By default when a DML statement fails all the records will be rolled back.
When you are loading millions of records and if there is one value which is unique then all the records which were inserted will be rolled back.
Finding out that one single record can be a daunting task.
Did you know, We can make use of DML error logging feature (introduced after Oracle 10G and above) to find out the record which is causing the error.

CREATE TABLE DML_TABLE (
id NUMBER(10) NOT NULL,
description VARCHAR2(50),
CONSTRAINT DML_TABLE_PK1 PRIMARY KEY (id)
);

INSERT INTO DML_TABLE VALUES (1,'FIRST VALUE');

INSERT INTO DML_TABLE VALUES (2,'SECOND VALUE');

INSERT INTO DML_TABLE VALUES (1,'SAME VALUE');

ORA-000001:Unique constraint Voilated

Now to avoid this and continue with the processing of records (when there are more than one record)

First we need to configure a table to hold errored out values:

BEGIN
DBMS_ERRLOG.create_error_log ('DML_TABLE','DML_TABLE_ERR');
END;

Suffix LOG ERRORS statement to the DML statement

INSERT INTO DML_TABLE VALUES (1,'SAME VALUE')
LOG ERRORS INTO DML_TABLE_ERR('INSERT') REJECT LIMIT UNLIMITED;

Now you wont get the error and the value will be inserted into ERR table.

select * from DML_TABLE_ERR;
This feature can be used for all the DML statements (INSERT,DELETE,UPDATE,MERGE)

Restriction:
When updating a record, if there is a unique constraint error then it cannot be logged.