This article presents extension for standard DML operations (INSERT, UPDATE, DELETE, MERGE) .. LOG ERRORS INTO. It enables to execute successfully DML operation into target table regardless of errors during processing of rows. Informations about errors are loaded together with rows content into dedicated error table.

Syntax

Here is general syntax for DML

INSERT/UPDATE/DELETE/MERGE ...
...
LOG ERRORS [INTO [schema_name.]table_name] [('simple_expression')] 
[REJECT LIMIT integer|UNLIMITED]

where

• schema_name.table_name – is error table created with DBMS_ERRLOG package
• simple_expression – is tag that can be applied to failed records. It’s stored in error table in column ORA_ERR_TAG$
• REJECT LIMIT specifies maximum number of accepted errors before the statment fails and rollback all. Default value is 0 and maximum UNLIMITED

Test data

To show how it works I need to prepare some test data.

--source table
CREATE TABLE test_tbl_src
(
  id1 number,
  id2 varchar2(10),
  id3 varchar2(20)
);

--target table
CREATE TABLE test_tbl_trg
(
  id1 NUMBER,
  id2 VARCHAR2(5) NOT NULL,
  id3 DATE
);

--dummy records
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';

INSERT INTO test_tbl_src VALUES(10, NULL,        SYSDATE);
INSERT INTO test_tbl_src VALUES(20, 'long name', SYSDATE);
INSERT INTO test_tbl_src VALUES(30, 'short',     SYSDATE);
INSERT INTO test_tbl_src VALUES(40, 'short',     '2014.01.01');
INSERT INTO test_tbl_src VALUES(50, 'short',     SYSDATE);

COMMIT;

SELECT * FROM test_tbl_src;

       ID1 ID2        ID3                
---------- ---------- --------------------
        10            07.10.2014           
        20 long name  07.10.2014           
        30 short      07.10.2014           
        40 short      2014.01.01
        50 short      07.10.2014           

Package DBMS_ERRLOG

Now it’s time to prepare error log table for target table TEST_TBL_TRG with package DBMS_ERRLOG. As default error table gets prefix name ERR$_.

BEGIN
  dbms_errlog.create_error_log
  (
    dml_table_name => 'TEST_TBL_TRG' 
  );
END;
/

New error table ERR$_TEST_TBL_TRG has got extra columns that store informations about encountered errors during DML operation

desc test_tbl_trg
Name Null     Type        
---- -------- ----------- 
ID1           NUMBER      
ID2  NOT NULL VARCHAR2(5) 
ID3           DATE        

desc err$_test_tbl_trg
Name            Null Type           
--------------- ---- -------------- 
ORA_ERR_NUMBER$      NUMBER         
ORA_ERR_MESG$        VARCHAR2(2000) 
ORA_ERR_ROWID$       UROWID()       
ORA_ERR_OPTYP$       VARCHAR2(2)    
ORA_ERR_TAG$         VARCHAR2(2000) 
ID1                  VARCHAR2(4000) 
ID2                  VARCHAR2(4000) 
ID3                  VARCHAR2(4000)

Insert

When data are loaded from TEST_TBL_SRC into TEST_TBL_TRG it fails because some of the records are wrong. It can be very frustrating especially when millions of rows are loaded and only a few records are wrong.

INSERT INTO test_tbl_trg
SELECT * FROM test_tbl_src;

SQL Error: ORA-01400: 
cannot insert NULL into ("TOMASZ"."TEST_TBL_TRG"."ID2")

So here comes help with LOG ERRORS INTO

INSERT INTO test_tbl_trg
SELECT * FROM test_tbl_src
LOG ERRORS INTO err$_test_tbl_trg ('INSERT') 
REJECT LIMIT UNLIMITED;

2 rows inserted.

COMMIT;

SELECT * FROM test_tbl_trg;

       ID1 ID2   ID3      
---------- ----- ----------
        30 short 07.10.2014 
        50 short 07.10.2014

and error table shows in detail why rest of data failed

SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ORA_ERR_MESG$ 
FROM err$_test_tbl_trg;

ORA_ERR_NUMBER$ ORA_ERR_TAG$ ORA_ERR_MESG$
--------------- ------------ --------------------------------------- 
1400            INSERT    
"ORA-01400: cannot insert NULL into ("TOMASZ"."TEST_TBL_TRG"."ID2")"

12899           INSERT    
"ORA-12899: value too large for column "TOMASZ"."TEST_TBL_TRG"."ID2" 
(actual: 9, maximum: 5)"

1861            INSERT    
"ORA-01861: literal does not match format string"

Update

Similar example for UPDATE statements. It fails because NULL can’t be applied into ID2 column.

SELECT * FROM test_tbl_trg;

       ID1 ID2   ID3      
---------- ----- ----------
        30 short 07.10.2014 
        50 short 07.10.2014

UPDATE test_tbl_trg
SET id2 = decode(id1, 30, id2, null);

ORA-01407: cannot update ("TOMASZ"."TEST_TBL_TRG"."ID2") to NULL

UPDATE test_tbl_trg
SET id2 = decode(id1, 30, id2, NULL)
LOG ERRORS INTO err$_test_tbl_trg ('UPDATE') 
REJECT LIMIT UNLIMITED;

1 rows updated.

COMMIT;

SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ORA_ERR_MESG$ 
FROM err$_test_tbl_trg
WHERE ORA_ERR_TAG$='UPDATE';

ORA_ERR_NUMBER$ ORA_ERR_TAG$ ORA_ERR_MESG$
--------------- ------------ ---------------------------------------
1407    UPDATE    
ORA-01407: cannot update ("TOMASZ"."TEST_TBL_TRG"."ID2") to NULL 

Delete

Similar example for DELETE statements. First trigger is created to block DELETE statement.

CREATE OR REPLACE TRIGGER TRIGGER1 
BEFORE DELETE ON TEST_TBL_TRG 
FOR EACH ROW
BEGIN
  IF :old.id1=30 THEN
    raise_application_error(-2000, 'can''t delete id1=30');
  END IF;
END;
/

DELETE FROM test_tbl_trg;
ORA-20000: can't delete id1=30

DELETE FROM test_tbl_trg
LOG ERRORS INTO err$_test_tbl_trg ('DELETE') 
REJECT LIMIT UNLIMITED;

1 rows deleted.

COMMIT

SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ORA_ERR_MESG$ 
FROM err$_test_tbl_trg
WHERE ORA_ERR_TAG$='DELETE';

ORA_ERR_NUMBER$ ORA_ERR_TAG$ ORA_ERR_MESG$
--------------- ------------ ---------------------------------------
20000    DELETE    
ORA-20000: can't delete id1=30
ORA-06512: at "TOMASZ.TRIGGER1", line 3
ORA-04088: error during execution of trigger 'TOMASZ.TRIGGER1'

Merge

Simple example for MERGE

ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';

MERGE INTO test_tbl_trg a
    USING test_tbl_src b
    ON (a.id1 = b.id1)
  WHEN MATCHED THEN
    UPDATE SET a.id2=b.id2,
               a.id3=b.id3
  WHEN NOT MATCHED THEN
    INSERT (id1, id2, id3)
    VALUES (b.id1, b.id2, b.id3);

ORA-01861: literal does not match format string

MERGE INTO test_tbl_trg a
    USING test_tbl_src b
    ON (a.id1 = b.id1)
  WHEN MATCHED THEN
    UPDATE SET a.id2=b.id2,
               a.id3=b.id3
  WHEN NOT MATCHED THEN
    INSERT (id1, id2, id3)
    VALUES (b.id1, b.id2, b.id3)
LOG ERRORS INTO err$_test_tbl_trg ('MERGE') 
REJECT LIMIT UNLIMITED;

2 rows merged.

SELECT ORA_ERR_NUMBER$, ORA_ERR_TAG$, ORA_ERR_MESG$ 
FROM err$_test_tbl_trg
WHERE ORA_ERR_TAG$='MERGE';

ORA_ERR_NUMBER$ ORA_ERR_TAG$ ORA_ERR_MESG$
--------------- ------------ ---------------------------------------
1861    MERGE    
ORA-01861: literal does not match format string

12899    MERGE    
ORA-12899: value too large for column "TOMASZ"."TEST_TBL_TRG"."ID2" 
(actual: 9, maximum: 5)

1400    MERGE    
ORA-01400: cannot insert NULL into ("TOMASZ"."TEST_TBL_TRG"."ID2")

Support

Oracle Database logs the following errors during DML operations:

• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints)
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors
• Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

Limitations

It has as well many restrictions causing full rollback on executed statement:

• You cannot execute this statement if table or the base table of view contains any domain indexes marked IN_PROGRESS or FAILED.
• You cannot insert into a partition if any affected index partitions are marked UNUSABLE.
• With regard to the ORDER BY clause of the subquery in the DML_table_expression_clause, ordering is guaranteed only for the rows being inserted, and only within each extent of the table. Ordering of new rows with respect to existing rows is not guaranteed.
• If a view was created using the WITH CHECK OPTION, then you can insert into the view only rows that satisfy the defining query of the view.
• If a view was created using a single base table, then you can insert rows into the view and then retrieve those values using the returning_clause.
• You cannot insert rows into a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
  • a set operator
  • a DISTINCT operator
  • An aggregate or analytic function
  • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
  • A collection expression in a SELECT list
  • A subquery in a SELECT list
  • A subquery designated WITH READ ONLY
  • Joins, with some exceptions, as documented in Oracle Database Administrator’s Guide
• If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, then the INSERT statement will fail unless the SKIP_UNUSABLE_INDEXES session parameter has been set to TRUE.

Have a fun 🙂

Tomasz