How to log errors while performing DML Operations through sql ?

1. First create source and target table

create table insertTestSourec

(

    id varchar(200)   

)

create table insertTestTarget

(id number

)

2. Create error log table using proc create_error_log

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'insertTestTarget');

END;

insert into insertTestSourec values(1)

insert into insertTestSourec values(2)

insert into insertTestSourec values('t')

insert into insertTestTarget

select * from insertTestSourec

Here, statement will get rollback and not a single record will get inserted into target table.

Check your error log table using below sql

select *

from all_tables

where table_name like upper('%insertTestTarget%')

3. Verify the error log table

insert into insertTestTarget

select * from insertTestSourec

LOG ERRORS INTO ERR$_INSERTTESTTARGET ('INSERT') REJECT LIMIT UNLIMITED;

4. Here, after execution of above statement, 2 records (1 and 2) will get inserted into target table and incorrect record will get inserted into ERR$_INSERTTESTTARGET.

select *

from ERR$_INSERTTESTTARGET