DML Error Logging in 10gR2

This is a cool stuff in oracle. I just tried it few days back and I was exited so thought of sharing it here.

Have you ever stuck up with a bulk insert failing on a check constraint? In such a situation we spend most of our time trying to find out the culprit rows. Oracle has come up with a great stuff by which such rows can be logged separately without disturbing the valid rows.

To put it straight by using the DML Error Logging feature you can just pick up all the invalid rows and put it separately and let the valid records go into the target table.Following is a small demonstration of this feature.

This is the source table from which the data is going to be selected.

SQL> CREATE TABLE SOURCE(NO NUMBER, NAME VARCHAR2(100))
2 /

Table created.

SQL> INSERT INTO SOURCE SELECT LEVEL, 'KARTHICK' FROM DUAL CONNECT BY LEVEL <=15
2 /

15 rows created.

This is the target table into which the data from the source table is going to be inserted.

SQL> CREATE TABLE TARGET(NO NUMBER, NAME VARCHAR2(100))
2 /

Table created.

I am creating a check constraint as follows

SQL> ALTER TABLE TARGET ADD CONSTRAINT SOURCE_CHK CHECK(NO <10)
2 /

Table altered.

Now let’s try to insert the source table data into target table.

SQL> INSERT INTO TARGET SELECT * FROM SOURCE;
INSERT INTO TARGET SELECT * FROM SOURCE
*
ERROR at line 1:
ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated

As we have a check constraint on column TARGET.NO which says NO should be less than 10, if we have any row which does not satisfy this condition then the entire insert fails. But I want the valid rows to be inserted into my target table and all the invalid rows to be logged separately. I am going to achieve this using the DML Error Logging technique.

To achieve that first we need to create a Error Log Table. The following command helps us in doing that.

SQL> BEGIN
2 DBMS_ERRLOG.create_error_log (dml_table_name => 'TARGET');
3 END;
4 /

PL/SQL procedure successfully completed.

This creates a new error log table for our TARGET table. The error log table is described below

SQL> desc err$_TARGET;

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)
NO VARCHAR2(4000)
NAME VARCHAR2(4000)

Now we need to use LOG ERROR INTO syntax in our INSERT INTO statement to log the error.

SQL> INSERT INTO TARGET SELECT * FROM SOURCE LOG ERRORS INTO err$_TARGET ('INSERT') REJECT LIMIT UNLIMITED
2 /

9 rows created.

Now you can observe that of the 15 rows 9 rows that satisfy the check constraints are inserted into the TARGET table.So the invalid records will be logged into the Error Log table that we have created above. Let’s have a look into the error log table.

SELECT * FROM ERR$_TARGET

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ NO NAME
--------------------------------------------------------------------------------------------------------------------------------------------
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 10 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 11 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 12 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 13 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 14 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 15 KARTHICK

Now which this information we can easily start our trouble shooting to figure out why the rows failed the check constraint.