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; |
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.
