Friday, September 17, 2010

How to log DML errors.

While performing any DML operation, if an error occurs, the statement is terminated and rolled back in its entirety. In case large DML operation it can be wasteful of time and system resources.
For such dml statements, you can avoid this situation by using the DML error logging feature.


example as:
Firstly we have to create DML error table. like

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('EMP', 'EMP_ERR');

PL/SQL procedure successfully completed.

SQL> DESC EMP_ERR;
 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)
 EMPNO                                              VARCHAR2(4000)
 ENAME                                              VARCHAR2(4000)
 JOB                                                VARCHAR2(4000)
 MGR                                                VARCHAR2(4000)
 HIREDATE                                           VARCHAR2(4000)
 SAL                                                VARCHAR2(4000)
 COMM                                               VARCHAR2(4000)
 DEPTNO                                             VARCHAR2(4000)

SQL> 


Or we can create Error log table manually.

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.)

Moreover some the errors will not be logged in log table as a result the DML operation will be stopped will error and roll backed.

SQL> INSERT INTO emp
  2    SELECT * FROM emp
  3    LOG ERRORS INTO EMP_ERR ('daily_load') REJECT LIMIT UNLIMITED;

0 rows created.

SQL> column ORA_ERR_MESG$ format a100
SQL> column ORA_ERR_TAG$  format  a50
SQL> column ENAME  format a50
SQL> column JOB  format a50
SQL> column SAL  format a50

SQL> select ORA_ERR_NUMBER$ ,ORA_ERR_MESG$ from emp_err;

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- -------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- --------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated

14 rows selected.

SQL> 


Like in example insert statement doesn't fail with error.. but it has not insert any record becuase due to primary key in emp table. If we query the error log table (EMP_ERR) then we can check the error message and column values for the query is failing.

DML error logging can work on almost all dml statements (Insert, Update, Delete, Merge) This type of error logging mechanism can be very beneficial in data warehouse environments.

Also the error logging table will be transaction independent. if the base dml statement is rollback then the data which is modified in main table (EMP) will be roll backed but the error log table (EMP_ERR) table data will not be roll backed.
like:
SQL> DELETE FROM EMP_ERR;

12 rows deleted.
SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

Table created.

SQL> UPDATE EMP1 SET EMPNO = ROWNUM WHERE ROWNUM <=2
  2  /

2 rows updated.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO EMP SELECT * FROM EMP1
  2     LOG ERRORS INTO EMP_ERR ('daily_load') REJECT LIMIT UNLIMITED;

2 rows created.

SQL> select count(*) from emp_err;

  COUNT(*)
----------
        12

SQL> select empno from emp;

    EMPNO
---------
        1
        2
     7369
     7499
     7521
     7566
     7654
     7698
     7782
     7788
     7839

    EMPNO
---------
     7844
     7876
     7900
     7902
     7934

16 rows selected.

SQL> roll
Rollback complete.
SQL> select count(*) from emp_err;

  COUNT(*)
----------
        12

SQL> select empno from emp;

    EMPNO
---------
     7369
     7499
     7521
     7566
     7654
     7698
     7782
     7788
     7839
     7844
     7876

    EMPNO
---------
     7900
     7902
     7934

14 rows selected.

SQL>


In above example after rollback changes from EMP table has been roll backed but the error messages in EMP_ERR are not rollbacked.

No comments: