Efficient error handling in Bulk Data Load process

When we are loading the data from different sources to target schema, we don’t want process to error out, because of a single record. We can filter out these error records & still continue the process.

AS a system user

SQL> exec dbms_errlog.create_error_log
(
dml_table_name=>’scott.dept’,
err_log_table_name=> ‘err_dept’,
err_log_table_owner=>’error_data’,
err_log_table_space=>’data_tbs’,
skip_unsupported=>true
);

Check if the error table is created

$ sqlplus error_data

SQL> select * from tab where tname = ‘ERR_DEPT’;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
ERR_DEPT TABLE

SQL> desc err_dept
Name Type
——————– —————-
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
DEPTNO VARCHAR2(4000)
DNAME VARCHAR2(4000)
LOC VARCHAR2(4000)

Note: whatever is the column datatype in the source, it is always created as VARCHAR2(4000) in error table

SQL> show user
USER is “error_data”

Grant insert on ERR_DEPT table to SCOTT

SQL> grant insert on err_dept to scott;

Grant succeeded.

Now the actual test

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

We know that DEPTNO is a PK in DEPT table. Let’s try inserting same DEPTNO, which will violate the PK constraint.

SQL> set serveroutput on

declare
begin
for i in 1..10
loop
insert into dept (deptno, dname, loc) values (i,’TEST’,’TEST’) log errors into error_data.err_dept reject limit 500;
end loop;
exception
when others then
dbms_output.put_line (substr(sqlerrm,1,100));
end;
/

PL/SQL procedure successfully completed.

Note: In this case, if the total number of error records exceeds 500, entire operation will be roll backed. This error limit is decided by reject limit clause

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1 TEST TEST
2 TEST TEST
3 TEST TEST
4 TEST TEST
5 TEST TEST
6 TEST TEST
7 TEST TEST
8 TEST TEST
9 TEST TEST

13 rows selected.

So it added the deptno from 1 to 9. But for deptno 10, record errored out & inserted into err_dept

$ sqlplus error_data

SQL> select ora_err_mesg$, deptno, dname, loc from err_dept
/

ORA_ERR_MESG$ DEPTNO DNAME LOC
————— —— —– —
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated 10 TEST TEST

Advertisements
This entry was posted in Oracle Bulk Data Load and tagged . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s