LOG ERRORS Oralce’s DML

Very powerfully and simple solution for 10g+ database.

This script is useful for data migration or transformation tasks where some records may fail due to data quality issues, but you want the process to continue for the rest. The failed records and their error reasons are saved for review, without halting the whole insert operation.

Would you like an example of how the ERR$_ table looks or how to query it for logged errors?

 DECLARE

  l_sql_statement    VARCHAR2(30000) := ' SELECT emp.*
      ,sal / comm sal_to_comm_ratio
FROM   scott.emp emp ';
  l_create_table_sql VARCHAR2(32767);
  l_insert_table_sql VARCHAR2(32767);
  l_table_name       VARCHAR2(30) := 'XXERR_TEST';
  l_error_table_name VARCHAR2(30) := 'ERR$_' || l_table_name;

BEGIN
  l_create_table_sql := 'CREATE TABLE ' || l_table_name ||  
                        ' AS SELECT * FROM (' || l_sql_statement ||
                        ') WHERE 1=2';
  

   
  EXECUTE IMMEDIATE l_create_table_sql;
  
  dbms_errlog.create_error_log(dml_table_name => l_table_name);
  
 l_insert_table_sql := 'INSERT INTO ' || l_table_name || ' SELECT * FROM (' || 
                        l_sql_statement || ') LOG ERRORS INTO err$_' 
                      ||l_table_name || ' (''INSERT'') REJECT LIMIT UNLIMITED';
   
  EXECUTE IMMEDIATE l_insert_table_sql;
  
  dbms_output.put_line('Error Messages are stored in table ' ||                           l_error_table_name);

END;

Source: Run Library Script