Handle error data during bulk insert/update operation in Oracle using DBMS ERRLOG package

DBMS ERRLOG is basically used in larger ETL (Extract, Transform, Load) process where we need to some time skips error during larger import.
Following DBMS_ERRLOG package will help us to identify what kind of error occurs during import process and it also help us to identified the rowid of the errors in table.

Example of using DBMS_ERRLOG package

Suppose we are performed bluk operation on employees table and we may have some data repeated or foregin constraint error. To handle that data from bulk load we will use dbms_errlog package.

1. We have to create the new table for DML operation called error log by using the package.
SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('employeeS');
PL/SQL procedure successfully completed.

Syntax:
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);


2. If you donot specify the err_log_table_name value keep it as null then you will automatically created ERR$_ as prefix as tablename as shown below:

SQL> desc ERR$_Employees;

 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)
 EMPLOYEE_ID                                        VARCHAR2(4000)
 FIRST_NAME                                         VARCHAR2(4000)
 LAST_NAME                                          VARCHAR2(4000)
 EMAIL                                              VARCHAR2(4000)
 PHONE_NUMBER                                       VARCHAR2(4000)
 HIRE_DATE                                          VARCHAR2(4000)
 JOB_ID                                             VARCHAR2(4000)
 SALARY                                             VARCHAR2(4000)
 COMMISSION_PCT                                     VARCHAR2(4000)
 MANAGER_ID                                         VARCHAR2(4000)
 DEPARTMENT_ID                                      VARCHAR2(4000)

3. Then you tried to insert the data into the table by specifying the log errors clause in your statement.

SQL> insert into employees select * from employees log errors into err$_employees REJECT LIMIT UNLIMITED;
insert into employees select * from employees log errors into err$_employees
*
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated


4. You will find the reject limit

SQL> SELECT ORA_ERR_NUMBER$ FROM ERR$_EMPLOYEES;

ORA_ERR_NUMBER$
---------------
1


If you donot specify the LOG ERRORS INTO clause in statment then it will not records error in your DBMS_ERRLOG table.

SQL> insert into employees select * from employees;
insert into employees select * from employees
*
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

SQL> SELECT * FROM ERR$_EMPLOYEES;
no rows selected