DML Error Logging Oracle 10g Release 2 feature

Oracle PL/SQL DML Error Logging using DBMS_ERRLOG Package

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


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.