Error logging using DBMS_ERRLOG
If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible. DBMS_ERRLOG, a package introduced in Oracle10g Release 2 , allows you to do precisely that. Here is a quick review of the way this package works. First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS. 1. Create an error log table for the table against which you will execute DML statements: BEGIN DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP'); END; Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it...