LOG ERRORS Oralce’s DML

Very powerfully and simple solution for 10g+ database.

 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