LOG ERRORS: load the good rows, keep the bad ones for later

A 10,000-row load with one bad row fails completely: one constraint violation rolls back the whole statement. LOG ERRORS changes that: good rows go in, bad rows go to an error table with the reason, and you deal with them in the morning.

The test case

A sponsor's spreadsheet of "new teams" arrives with problems: a duplicate code, a code that's too long, and a team with no name. Load what's valid into a teams table (code must be unique and exactly three letters; name required) and log the rest.

Setup

begin
  for t in (select table_name from user_tables where table_name in ('D95_TEAMS', 'ERR$_D95_TEAMS', 'D95_STAGE', 'D95_TEAMS_B')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d95_stage as
select 7 as team_id, 'LGS' as team_code, 'Lagos Lanterns' as team_name from dual union all
select 8,  'VAN',  'Vancouver Voyagers'  from dual union all
select 9,  'LGS',  'Lagos Lions'         from dual union all
select 10, 'NAIR', 'Nairobi Nightjars'   from dual union all
select 11, 'DXB',  null                  from dual union all
select 12, 'CPT',  'Cape Town Cormorants' from dual;

Setup

create table d95_teams (
  team_id   number primary key,
  team_code varchar2(3) not null unique,
  team_name varchar2(40) not null
);

Setup

create table d95_teams_b as select * from d95_teams;

Setup

begin
  dbms_errlog.create_error_log(dml_table_name => 'D95_TEAMS');
end;
/

DBMS_ERRLOG.CREATE_ERROR_LOG makes the error table ERR$_D95_TEAMS: the same columns (as text) plus ORA_ERR_NUMBER$, ORA_ERR_MESG$ and a few more.

Way 1: INSERT ... LOG ERRORS

Setup

insert /*+ noappend noparallel */ into d95_teams (team_id, team_code, team_name)
select team_id, team_code, team_name from d95_stage order by team_id
log errors into err$_d95_teams ('sponsor file') reject limit unlimited;

What loaded

select team_id, team_code, team_name from d95_teams order by team_id;

What didn't, and why

select team_id, team_code, ora_err_number$, ora_err_tag$
from   err$_d95_teams
order  by to_number(team_id);

• ORA-00001: unique constraint violated (second LGS).
• ORA-12899: value too large for the column (NAIR is four characters).
• ORA-01400: cannot insert null (no name).
• The tag 'sponsor file' labels this load's errors, so several loads can share one error table.
• REJECT LIMIT UNLIMITED keeps going however many rows fail; the default limit is 0, which logs one error and then fails anyway.
• /*+ noappend noparallel */ keeps the insert conventional (row by row). See the first gotcha.

Way 2: filter the bad rows out first

Setup

insert into d95_teams_b (team_id, team_code, team_name)
select s.team_id, s.team_code, s.team_name
from   d95_stage s
where  length(s.team_code) = 3
and    s.team_name is not null
and    not exists (select 1 from d95_stage x where x.team_code = s.team_code and x.team_id < s.team_id);

select team_id, team_code, team_name from d95_teams_b order by team_id;

Same rows loaded, no error table, but you had to know every rule in advance and write it again in the WHERE. LOG ERRORS lets the table's own constraints do the checking.

19c and 26ai

The same on both.

Gotchas

• Direct-path and parallel inserts can't log unique-key violations. The whole statement fails with ORA-00001 (or, in parallel, ORA-12801). Autonomous Database runs DML in parallel by default, which is exactly what happened when we first tested this page on 19c ADB. Hence the hint.

• Error columns are text. TEAM_ID in the error table is VARCHAR2, so sort it with TO_NUMBER (as above) or you'll get 10 before 9.

• Some errors aren't logged: a deferred constraint failing at commit, or space errors, still fail the statement.

• The error table isn't emptied for you. Clear it (or filter by tag) before the next load.