DML ERROR LOGGING an ORACLE 10gR2 New Feature

If a constraint violation raises a runtime error during execution of a DML statement we encounter a statement level rollback.
Oracle server creates a so called implicit savepoint before it excutes a DML statement, to which it can automatically rollback.
This can be very time and resource consuming especially with bulk DML operations.

Starting with Oracle database 10g release 2 it is possible to let a DML statement continue without erroring out although constaint violations are created.
For this we can use the new PL/SQL-package DBMS_ERRLOG to create an error logging table which can be used to capture information about rows which have caused a constraint violation during DML-execution.
Additionally we can define a limit of violations which will cause the statement to fail.
If the rejected rows are less than the specified limit the statement will succeed despite the contraint violations.

Here is a little demo:
– create a test table
HR @10gR2 SQL > create table error_test
(
col1 number,
col2 number constraint error_check check (col2 > 100)
);
Table created.

– populate the test table
HR @10gR2 SQL > BEGIN
2 FOR i IN 100..200 loop
3 INSERT INTO error_test
4 VALUES (i, i+50);
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.

– create the error log table
HR @10gR2 SQL > exec DBMS_ERRLOG.CREATE_ERROR_LOG(’ERROR_TEST’,'error_log_tab’)
PL/SQL procedure successfully completed.

HR @10gR2 SQL > desc error_log_tab
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)
COL1 VARCHAR2(4000)
COL2 VARCHAR2(4000)

– cause constraint violations less than reject limit
HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 80;
50 rows updated.
– look at the error loggin table
HR @10gR2 SQL > SELECT ora_err_number$,
ora_err_mesg$,
ora_err_rowid$,
col1,
col2
FROM error_log_tab;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ COL1 COL2
————— —————————————————- ———————- ———- ———-
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAA 100 50
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAB 101 51
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAC 102 52
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAD 103 53
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAE 104 54
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAF 105 55
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAG 106 56
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAH 107 57
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAI 108 58
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAJ 109 59
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAK 110 60
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAL 111 61
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAM 112 62
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAN 113 63
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAO 114 64
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAP 115 65
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAQ 116 66
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAR 117 67
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAS 118 68
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAT 119 69
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAU 120 70
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAV 121 71
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAW 122 72
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAX 123 73
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAY 124 74
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAZ 125 75
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAa 126 76
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAb 127 77
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAc 128 78
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAd 129 79
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAe 130 80
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAf 131 81
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAg 132 82
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAh 133 83
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAi 134 84
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAj 135 85
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAk 136 86
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAl 137 87
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAm 138 88
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAn 139 89
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAo 140 90
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAp 141 91
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAq 142 92
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAr 143 93
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAs 144 94
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAt 145 95
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAu 146 96
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAv 147 97
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAw 148 98
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAx 149 99
2290 ORA-02290: check constraint (HR.ERROR_CHECK) violated AAANX1AAEAAAAGEAAy 150 100

51 rows selected.
– rollback
HR @10gR2 SQL > rollback;

– cause constraint violations more than reject limit
HR @10gR2 SQL > HR @10gR2 SQL > UPDATE error_test
2 SET col2=col2-100
3 LOG ERRORS INTO error_log_tab
4 REJECT LIMIT 20;
UPDATE error_test
*
ERROR at line 1:
ORA-02290: check constraint (HR.ERROR_CHECK) violated

connect lutz hartmann as sysdba