A DML statement is all or nothing: one bad row among a million makes the whole INSERT fail and roll back. When loading imperfect data, it is often better to load the good rows and set the bad ones aside. DML error logging does exactly that: rows that fail go into an error table with the error number and message, and the statement continues.
Code for This Guide
The main examples are in the examples/dml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
exec dbms_errlog.create_error_log('TABLE') -- creates ERR$_TABLE
insert | update | delete | merge ...
log errors [into error_table] [('tag')] [reject limit {n | unlimited}]DBMS_ERRLOG.CREATE_ERROR_LOG creates the error table, named ERR$_ followed by the table name, with the table's columns plus ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_TAG$, and a few more. The tag labels the rows of one load. REJECT LIMIT is how many errors are allowed before the statement fails; the default is 0.
Load the Good Rows, Log the Bad
Four new routes from Dubai are inserted, but three of them already exist and break the unique key on origin and destination.
Example:
exec dbms_errlog.create_error_log('ROUTES')
insert into routes (origin, destination, distance_km, block_minutes)
select 'DXB', airport_code, 1000, 100 from airports where country_code in ('NP', 'IN')
log errors into err$_routes ('load of 26 Sep') reject limit unlimited;
select ora_err_number$ as error, ora_err_tag$ as tag, destination from err$_routes;
rollback;Output:
PL/SQL procedure successfully completed.
1 row inserted.
ERROR TAG DESTINATION
________ _________________ ______________
1 load of 26 Sep BOM
1 load of 26 Sep DEL
1 load of 26 Sep BLR
Rollback complete.One row is inserted. The three duplicates are in ERR$_ROUTES with error 1, ORA-00001, and the tag of the load. The example rolls back and drops the error table afterward.
When the Limit Is Reached
Once the number of errors exceeds the reject limit, the statement fails and is rolled back as usual.
Example:
-- with REJECT LIMIT 1, a second bad row stops the statement and undoes it
insert into fare_load
select route_id, case when route_id in (2, 3) then -1 else 500 end from routes where route_id <= 5
log errors into err$_fare_load ('limit test') reject limit 1;
select count(*) as loaded from fare_load;Output:
Error starting at line : 2
In command -
insert into fare_load
select route_id, case when route_id in (2, 3) then -1 else 500 end from routes where route_id <= 5
log errors into err$_fare_load ('limit test') reject limit 1
Error report -
ORA-02290: check constraint (NIMBUS.FARE_LOAD_CK) violated
LOADED
_________
0With REJECT LIMIT 1 and two bad rows, the statement fails on the second and nothing is loaded. The example creates and drops its own table and error table.
Things to Know
- The error table is written autonomously, so its rows remain even if the statement or transaction is rolled back.
- Unique and primary key violations in direct-path inserts are not logged; the statement fails as usual.
- Clear or tag the error table per load, so errors from different runs can be told apart.
Related Guides
Conclusion
LOG ERRORS sends rows that fail in an INSERT, UPDATE, DELETE, or MERGE to an error table created with DBMS_ERRLOG, so the rest of the statement succeeds. Set a REJECT LIMIT that suits the load, tag each run, and review the error table afterward.
