The DML error logging represents a nice feature introduced with oracle database 10gR2. Unfortunately this feature has several restrictions as described by the Oracle documentation reproduced here below:
The following conditions cause the statement to fail and roll back without invoking the error logging capability:
- Violated deferred constraints.
- Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
- Any update operation UPDATE or MERGE that raises a unique constraint or index violation).
- You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.
However, I have recently experienced a new restriction which is not mentioned in the Oracle documentation. Let’s see what it is about
sql> CREATE TABLE t (ID NUMBER PRIMARY KEY, text VARCHAR2(100)); Table created. sql> BEGIN 2 DBMS_ERRLOG.create_error_log (dml_table_name => 't'); 3 END; 4 / PL/SQL procedure successfully completed. sql>> INSERT INTO t 2 SELECT ROWNUM, 'Test DML' 3 FROM DUAL 4 CONNECT BY LEVEL <= 10 5 LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED; 10 rows created. sql> commit; Commit complete.
I will now create an after statement trigger as follows
</pre> sql> CREATE OR REPLACE TRIGGER t_trg 2 AFTER INSERT 3 ON t 4 DECLARE 5 my_exception EXCEPTION; 6 BEGIN 7 NULL; 8 RAISE my_exception; 9 EXCEPTION 10 WHEN my_exception 11 THEN 12 DBMS_OUTPUT.put_line ('Trigger t_trg raised My_exception'); 13 RAISE; 14 END; 15 / Trigger created. sql> set serveroutput on sql> INSERT INTO t 2 SELECT 11, 'Test DML after Statement Trigger' 3 FROM DUAL 4 LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED; Trigger t_trg raised My_exception LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED * ERROR at line 4: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "T_TRG", line 10 ORA-04088: error during execution of trigger 'T_TRG' sql> select count(1) from err$_t; COUNT(1) ---------- 0 sql> rollback; Rollback complete.
Woo, the DML error logging stops working correctly and raised an exception. The DML err$_t table is empty.
But let’s change the trigger to be a ‘for each row’ trigger
sql> CREATE OR REPLACE TRIGGER t_trg 2 AFTER INSERT 3 ON t 4 FOR EACH ROW 5 DECLARE 6 my_exception EXCEPTION; 7 BEGIN 8 NULL; 9 RAISE my_exception; 10 EXCEPTION 11 WHEN my_exception 12 THEN 13 DBMS_OUTPUT.put_line ('Trigger t_trg raised My_exception'); 14 RAISE; 15 END; 16 / Trigger created. sql> INSERT INTO t 2 SELECT 11, 'Test DML after Statement Trigger' 3 FROM DUAL 4 LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED; Trigger t_trg raised My_exception 0 rows created. sql>> select count(1) from err$_t; COUNT(1) ---------- 1
And this time the DML error logging table worked correctly.
Footnote: DML error logging has another restriction which is not mentioned by the Oracle documentation: it will not work with a statement trigger. It works only with a ‘for each row’ trigger.