DML Error Logging

By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected.

 Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors.

LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

The simple_expression is used to specify a tag that makes the errors easier to identify.

For parallel DML operations, the reject limit is applied to each parallel server.


 A log table must be created for every base table that requires the DML error logging functionality.

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => '<tablename>');
END;
/

log table name matches the first 25 characters of the base table with the "ERR$_" prefix





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

Insert :


INSERT INTO destinationTab
SELECT *
FROM   sourceTab
LOG ERRORS  ('INSERT') REJECT LIMIT UNLIMITED;


Update :

Update destinationTab set col1=xxx,col2=yyyy,col3=zzzz where id between 1 to 1000
Log Errors ('UPDATE') Reject Limit Unlimited;



Merge :

Merge into destinationTab d
using SourceTab s
on (s.id=d.id)
When matched then 
  update set d.col1=s.col1,d.col2=s.col2
When not matched then
  insert (id,col1,col2) values (s.id,s.col1,s.col2)
Log Errors ('Merge') Reject Limit 10;


Delete :

Delete destinationTab where id between 1 and 1000
Log Errors ('Delete') Reject Limit 100;