How to enable DML logging

DML logging can be used as a workaround if you have a large number of rows to process, and you expect that some of these will fail to be inserted correctly due to data quality problems. In other words, some rows may violate one or more constraints on the receiving table.

Here is a short demonstration that I have recently tested as a workaround for a client.

I have a table called MYTABLE, which looks like this:

 Name                   Null?    Type
 --------------------- -------- -------------------
 ORDER_ID                        NOT NULL NUMBER
 ORG_ORDERID                     NUMBER
 VALUE_ID_TYPE                   VARCHAR2(20)
 COL_PREFIX                      VARCHAR2(80)
 COL_NAME                        VARCHAR2(500)
 COL_SUFFIX                      VARCHAR2(20)

There is a unique constraint on the table:

--create the index
CREATE UNIQUE INDEX C_COL_UIX ON MYTABLE
(ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
TABLESPACE USERS;

-- create the constraint, using index created above
ALTER TABLE MYTABLE ADD (
  CONSTRAINT C_COL_CONS
  UNIQUE (ORG_ORDERID, COL_PREFIX, COL_NAME, COL_SUFFIX)
  USING INDEX C_COL_UIX);

The actual DML logging is performed by adding the statement

LOG ERRORS INTO error_table ('INSERT') REJECT LIMIT UNLIMITED;

at the end of the INSERT statement.

Before we can do this we need to create the error log table:

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

The statement above will create a table called ERR$_MYTABLE, where rejected rows will be inserted into.

For this test, I will now verify that the values I intend to use, actually exist in the table:

select count(*)
from mytable
where org_orderid = 122
and col_prefix = 'A'
and col_name = 'B'
and col_suffix = 'X'
;

  COUNT(*)
----------
         1

Any attempt to insert a row into the table with the same values as in the select statement above (122, A, B, X), will violate constraint C_COL_CONS, and logged in the table ERR$_MYTABLE.

Let's execute an INSERT statement that will violate the constraint:

INSERT
INTO mytable (order_id, col_prefix, col_name, col_suffix, col_type)
VALUES(122, 'A', 'B', 'X', 'D')
LOG ERRORS INTO ERR$_MYTABLE ('INSERT') REJECT LIMIT UNLIMITED;

As expected, I get the following error:

ORA-00001: unique constraint (C_COL_CONS) violated

Let's check the log table:

SELECT * FROM ERR$_MYTABLE;

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	ORDER_ID	ORG_ORDERID	VALUE_ID_TYPE	COL_PREFIX	COL_NAME	COL_SUFFIX
1	ORA-00001: unique constraint (C_COL_CONS; violated		I	INSERT	2850841	122	D	A	B	X

If you execute a batchjob with thousands of rows, the offending rows will be caught so they can be corrected later. The non-offending rows will be neatly inserted where they belong.