DML Error Logging is a new feature of Oracle 10g R2. You must have tried an insert-into-select statement that got aborted after 30 minutes because one column value is too large? With DML error logging, we can have all correct records inserted successfully in our table, and bad records to be written in log table to resolve the issue later.
The CREATE_ERROR_LOG procedure of DBMS_ERRLOG package makes you enables to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back.
Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_errlog.htm
Following is the syntax of DBMS_ERRLOG.CREATE_ERROR_LOG
DBMS_ERRLOG.CREATE_ERROR_LOG
(
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2
)
If "err_log_table_name" is not specified that the log table will be created with first 25 characters of dml_table_name table prefixed with 'ERR$_'
Lets say we have following table, and we wanto to log the DML error on it:
Here ERR$_EMPLOYEE table has been created by DBMS_ERRLOG.CREATE_ERROR_LOG, to record DML Error logs of EMPLOYEE table.
Lets say if some-one try to insert following command,
It will insert 12 rows out of 14 of SCOTT.EMP table, 2 got rejected due to the check constraint, which can be review after the command execution as:
DML Error Logging Handles following exceptions:
The CREATE_ERROR_LOG procedure of DBMS_ERRLOG package makes you enables to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back.
Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_errlog.htm
Following is the syntax of DBMS_ERRLOG.CREATE_ERROR_LOG
DBMS_ERRLOG.CREATE_ERROR_LOG
(
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2
)
If "err_log_table_name" is not specified that the log table will be created with first 25 characters of dml_table_name table prefixed with 'ERR$_'
Lets say we have following table, and we wanto to log the DML error on it:
SQL> CREATE TABLE EMPLOYEE
2 (
3 EMPID NUMBER(10),
4 ENAME VARCHAR2(100),
5 DEPTNAME VARCHAR2(100),
6 SALARY NUMBER(10) CHECK (SALARY>=1000 AND SALARY<=5000)
7 );
Table created.
SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('employee');
PL/SQL procedure successfully completed.
SQL> desc ERR$_EMPLOYEE;
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)
EMPID VARCHAR2(4000)
ENAME VARCHAR2(4000)
DEPTNAME VARCHAR2(4000)
SALARY VARCHAR2(4000)
Here ERR$_EMPLOYEE table has been created by DBMS_ERRLOG.CREATE_ERROR_LOG, to record DML Error logs of EMPLOYEE table.
Lets say if some-one try to insert following command,
SQL> INSERT INTO EMPLOYEE
2 SELECT EMPNO, ENAME, DNAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D
3 WHERE E.DEPTNO=D.DEPTNO
4 LOG ERRORS INTO ERR$_EMPLOYEE REJECT LIMIT UNLIMITED;
12 rows created.
It will insert 12 rows out of 14 of SCOTT.EMP table, 2 got rejected due to the check constraint, which can be review after the command execution as:
SQL> COL ORA_ERR_MESG$ FOR A50
SQL> COL ENAME FOR A25
SQL> COL SALARY FOR A10
SQL> SELECT ORA_ERR_MESG$, ENAME, SALARY FROM ERR$_EMPLOYEE;
ORA_ERR_MESG$ ENAME SALARY
-------------------------------------------------- -------------------- ----------
ORA-02290: check constraint (NIMISH.SYS_C0030353) SMITH 800
violated
ORA-02290: check constraint (NIMISH.SYS_C0030353) JAMES 950
violated
DML Error Logging Handles following exceptions:
- Too-large column values
- Constraint violations
- Trigger execution errors
- Type conversion errors
- Partition mapping errors
This is an excellent stuff. Keep posting..
ReplyDeleteVery good..It is more helpful..Pls share that what is actually using in real time scenario..so it is more helpful for extra.
ReplyDeleteThanks,