Dml error logging

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

Compiling Objects When to use a Slash

Compiling Objects When to use a slash ‚/‘

There was always a diffuse toppic for me in sqlscripts for sqlplus:
When to use a forwardslash (‚/‘) after a code-block and when not.

A simple marker:

Use a slash after every PL/SQL -block:

– Package
– Procedure
– Function

Example:
create or replace procedure procname as
null;
end procname;
/

Use NO slash after all DML -blocks:

– Table
– Index
– Constraint
– Sequence
– View
– Materialized View
– …

If there is an ‚Create or replace‘ in the first line of the code to compile there is no problem, but the object will be recreated unnecessarily.
But in the case of an index for example there will be an exception.