Oracle PL/SQL DML Error Logging using DBMS_ERRLOG Package

DML Error Logging, instead of the 100,000 row update/insert/whatever failing because a single row doesn't quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table!

Below is the example with data of all_objects into table de_big_tbl with an extra column id with Primary key constraint.

EXEC dbms_errlog.create_error_log('tbl');

DECLARE
    CURSOR c IS
    SELECT id,object_id, owner, object_name, subobject_name, data_object_id,
           DECODE( MOD(rownum,100000), 1, RPAD('*',20,'*'), object_type ) object_type,
           created, last_ddl_time, timestamp, status, temporary, generated, secondary
      FROM de_big_tbl;
    TYPE array IS TABLE OF c%ROWTYPE;
    l_data array;

    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381);
    l_errors NUMBER;
    l_errno  NUMBER;
    l_msg    VARCHAR2(4000);
    l_idx    NUMBER;
BEGIN
    OPEN c;
    LOOP
        FETCH c BULK COLLECT INTO l_data LIMIT 100;
        BEGIN
            FORALL i IN 1 .. l_data.COUNT SAVE EXCEPTIONS
                INSERT INTO tbl VALUES l_data(i);
        EXCEPTION
            WHEN dml_errors THEN
                l_errors := SQL%BULK_EXCEPTIONS.COUNT;
                FOR i IN 1 .. l_errors
                LOOP
                    l_errno := SQL%BULK_EXCEPTIONS(i).error_code;
                    l_msg   := SQLERRM(-l_errno);
                    l_idx   := SQL%BULK_EXCEPTIONS(i).error_index;
                    INSERT INTO err$_tbl
                    ( ora_err_number$, ora_err_mesg$, ora_err_optyp$,id,
                      object_id, owner, object_name, subobject_name,
                      data_object_id, object_type, created, last_ddl_time,
                      timestamp, status, temporary, generated, secondary )
                    VALUES
                    ( l_errno, l_msg, 'I',
                      l_data(l_idx).id,
                      l_data(l_idx).object_id,
                      l_data(l_idx).owner,
                      l_data(l_idx).object_name,
                      l_data(l_idx).subobject_name,
                      l_data(l_idx).data_object_id,
                      l_data(l_idx).object_type,
                      l_data(l_idx).created,
                      l_data(l_idx).last_ddl_time,
                      l_data(l_idx).timestamp,
                      l_data(l_idx).status,
                      l_data(l_idx).temporary,
                      l_data(l_idx).generated,
                      l_data(l_idx).secondary );
                END LOOP;
        END;
        EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END;

SELECT ora_err_number$,ora_err_mesg$,ora_err_optyp$ FROM err$_tbl;

Output :

ora_err_number$ ora_err_mesg$                               ora_err_optyp$
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I
1	            ORA-00001: unique constraint (.) violated	I

The above code can also be replaced with:

exec dbms_errlog.create_error_log('tbl');


INSERT /*+ append */
  INTO tbl
SELECT id, owner, object_name, subobject_name, object_id, data_object_id,
       DECODE( mod(rownum,100000), 1, RPAD('*',20,'*'), object_type ) object_type,
       created, last_ddl_time, timestamp, status, temporary, generated, secondary
  FROM de_big_tbl
  LOG ERRORS REJECT LIMIT UNLIMITED;

Below are some other examples to use create error log for DMLs.

ALTER TABLE emp ADD CONSTRAINT emp_check_sal CHECK(sal > 50); 

SELECT * FROM emp;

eid     mid     ename   sal

5550228	1110299	Krish	450
5550229	1110299	Krush	100
5550609	1110299	Shyam	100
1110299	1110299	Ram     200
5556431	1110222	Abhi	300
1110222	1110222	Raju	400

EXEC dbms_errlog.create_error_log('emp');

 UPDATE emp
 SET sal = sal - 50
 WHERE sal BETWEEN 100 and 3000
 LOG ERRORS ('Update Sal') 
 REJECT LIMIT UNLIMITED;
 
 SELECT ora_err_number$,
       ora_err_optyp$,
       ora_err_tag$,
       eid,
       ora_err_mesg$
  FROM err$_emp;
  
  ora_err_number$   ora_err_optyp$  ora_err_tag$    eid     ora_err_mesg$
  
  2290	            U	            Update Sal	    5550229	ORA-02290: check constraint (DEEXAMS.EMP_CHECK_SAL) violated

  2290	            U	            Update Sal	    5550609	ORA-02290: check constraint (DEEXAMS.EMP_CHECK_SAL) violated