Finding errors in Oracle

It is possible to trap errors in the loading process. This is handy when we work on a migration project. We simply load a table and trap all possible errors.

Let us use a simple table (emp_c) that must be loaded. However, one field is too narrow which loads to errors. These error will be trapped in table err$_emp_c. When loading the table, an option is added that leads errors to this table.

drop table emp_c;
create table emp_c
( employee_id number,
 ename varchar2(17)
 );
exec dbms_errlog.create_error_log('emp_c');
desc err$_emp_c;
insert into emp_c
select employee_id , first_name||' '||last_name name
from hr.employees
log errors reject limit unlimited;
select * from err$_emp_c;
truncate table err$_emp_c;

After loading, we may investigate the errors with a simple select statement.