Dml error logging

DML-Error-Logging is a feature available since ORACLE DB 10.2.

Creating the error-log-table:

exec dbms_errlog.create_error_log('tablename','tablename_err') ;

Logging errors at insert/update/merge/delete-statement:

Structure of the Error-Log-Table

Name Null? Type
----------------------------------------- -------- ----------------------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
VARCHAR2(4000)
VARCHAR2(4000)
VARCHAR2(4000)
<...>

Example:

insert into tablename (att1, att2, att3)
values (i, j, k)
LOG ERRORS INTO tablename_err REJECT LIMIT 1;

REJECT LIMIT: threshold amount of errors the statement shall stop. That means how many errors are allowed… Possible values: Integer/“UNLIMITED“

Default Value is 0! So it is always necessary to add this for a real error-logging. By default the first error is logged into the error-table and the dml-action is aborted.

Comment or Tagging of the error

insert into tablename (att1, att2, att3)
values (i, j, k)
LOG ERRORS INTO tablename_err
(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')) REJECT LIMIT 50;

There is the possibility to have an comment on the logged error in brackets after the error-logging-table (here filled with sysdate). This comment is inserted in the error-logging-table-attribute ORA_ERR_TAG$ VARCHAR2(2000 BYTE). It is needed to convert the content into varchar2!

Oracle-Docu 10g:
http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#sthref2223
Oracle-Docu 11gR2:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN10261
Oracle-Docu 12gR1:
http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN-GUID-36DB026B-4702-477A-92C4-EA2795D2B37F