Oracle DML Error Logging

Oracle DML Error Logging

Key Points of DML Error Logging
• DML SQL Query fails even if 1 row does not meet criteria or violates a constraint
• 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
• It is intuitive and errors are logged into error log table
• Feature available with Oracle database 10g release 2

-When you need to load millions of rows of data into a table, we use INSERT , UPDATE, MERGE .Similarly , delete for deleting bulk amount of rows.
-So when loading millions of rows into a table, a single integrity or constraint violation leads to failure of the statement and rollback . 
-No matter you loaded only one or 90000 out of 100000 rows , statement fails and rollback will be performed. In these situations, you need to have data either error free or efficient way to handle errors.
-You can take advantage using SQL* Loader if your data is in a file.
-However, if you have data in table or other objects ,you can use an anonymous block to handle errors , and you can use bulk collect , forall features for performance.
-A direct path INSERT DML is much faster than the above approach.

You need to create  error logging table for this DML error logging.

Syntax for creating error logging table.

DBMS_ERRLOG.CREATE_ERROR_LOG (
        dml_table_name                  IN VARCHAR2,
        err_log_table_name              IN VARCHAR2 := NULL,
        err_log_table_owner             IN VARCHAR2 := NULL,
        err_log_table_space             IN VARCHAR2 := NULL,
        skip_unsupported                IN BOOLEAN  := FALSE);

-All the parameters except DML_TABLE_NAME are optional.
-If the optional details are omitted, the name of the error logging table will be ERR$_ together with the first 25 characters of the DML_TABLE_NAME.
-The SKIP_UNSUPPORTED parameter, if set to TRUE, instructs the error logging clause to skip over LONG, LOB, and object type columns that are not supported and omit them from the error logging table.

LOG ERRORS SYNTAX:
With the error logging table created, you can add the error logging clause to most DML statements,  

LOG ERRORS [INTO [schema.]table] 
[ (simple_expression) ] 
[ REJECT LIMIT  {integer|UNLIMITED} ]


-The INTO clause is optional;
-If you omit it, the error logging clause will put errors into a table with the same name format used by the CREATE_ERROR_LOG procedure.
-SIMPLE_EXPRESSION is any expression that would evaluate to a character string and is used for tagging rows in the error table to indicate the process that caused the error, the time of the data load, and so on.
-REJECT LIMIT can be set to any integer or UNLIMITED and specifies the number of errors that can occur before the statement fails.
-This value is optional, but if it is omitted, the default value is 0, which effectively disables the error logging feature.


The following types of errors are handled by the error logging clause: 
Column values that are too large
Constraint violations (NOT NULL, unique, referential, and check constraints), except in certain circumstances detailed below
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


The following conditions cause the statement to fail and roll back without invoking the error logging capability: 
Violated deferred constraints
Out-of-space errors
Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation



we are using scott schema to demonstrate 

Create a regular table
SQL> create table emp_test as select * from scott.emp where 1=0;

Add Primary Key
SQL> alter table emp_test add constraint emp_pk primary key(empno);

Add Constraint
SQL> alter table emp_test add constraint emp_check_sal check(sal > 900);

Create error log table
SQL> exec dbms_errlog.create_error_log( ‘emp_test' );


SQL> desc err$_emp_test
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> insert into emp_test select * from emp; --Note DML fails because we have added check constraint
SQL> select * from emp_test; --Note no rows displayed
SQL> insert into emp_test select * from emp
LOG ERRORS REJECT LIMIT UNLIMITED ; --Note DML succeeds
SQL> select * from emp_test; --Note rows displayed
SQL> select ora_err_number$, ora_err_tag$,
ora_err_optyp$,empno,ora_err_mesg$ from err$_emp_test;
SQL> update emp_test set sal = sal - 2000 where sal between 2000 and 3000
LOG ERRORS ('My Update') REJECT LIMIT UNLIMITED;