DBMS ERRLOG package helped in ETL jobs in Oracle

DBMS ERRLOG package using ETL process in Oracle

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

1. We have to create the table for 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.