Example
Create a table (tq84_src) which will contain the data that is attempted to be filled into the destination table tq84_dest:
create table tq84_src (
id number(2),
txt varchar2(20) not null,
num number
);
create table tq84_dest (
id number(2),
txt varchar2(20) not null,
num number,
constraint tq84_err_log_pk primary key(id)
);
Create the error table:
begin
dbms_errlog.create_error_log(
'tq84_dest',
'tq84_dest_err'
);
end;
/
Fill the source table:
insert into tq84_src values (1, 'def' , 7);
insert into tq84_src values (2, 'nop' , 9);
insert into tq84_src values (3, 'abc' , 0);
insert into tq84_src values (4, 'klm' , 6);
insert into tq84_src values (5, 'nop' , 6);
insert into tq84_src values (6, 'qrs' , 9);
insert into tq84_src values (4, 'tuv' , 2);
Try to insert data into the dest table. Without error log clause, the first statement would fail with a primary key violation and a division by zero error:
begin
insert into tq84_dest
select
id,
txt || '!',
42/num
from
tq84_src
log errors into tq84_dest_err
--
-- Log all errors:
--
reject limit unlimited;
insert into tq84_dest values (99, '2nd statement', 42);
end;
/
Because of the error log clause, we can find the errors in the error log table:
select
ora_err_number$,
ora_err_mesg$,
id,
txt,
num
from
tq84_dest_err;