Oracle DBMS_ERRLOG
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Provides a procedure that enables creating an error logging table so that DML operations can continue after encountering errors rather than performing an abort and rollback. Tables with LONG, CLOB, BLOB, BFILE, and ADT datatypes are not supported.

... LOG ERRORS - effectively it turns array processing into single row processing, so it adds an expense at the moment of inserting, even though it saves you the overhead of an array rollback if a duplicate gets into the data.
 ~ Jonathan Lewis / comp.databases.oracle.server / 13-Aug-2006
AUTHID CURRENT_USER
Dependencies
DBMS_ASSERT DBMS_STANDARD KUPD$DATA
DBMS_OUTPUT DBMS_UTILITY PLITBLM
DBMS_SQL    
Documented Yes
First Available 10.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmserlg.sql
 
CREATE_ERROR_LOG
Variable Definition dbms_errlog.create_error_log(
dml_table_name      IN VARCHAR2,
err_log_table_name  IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported    IN BOOLEAN  := FALSE);
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT *
FROM all_tables
WHERE 1=2;

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (owner, table_name)
USING INDEX;

ALTER TABLE t
ADD CONSTRAINT cc_t
CHECK (blocks < 11);

col blocks format 99999

SELECT blocks, COUNT(*)
FROM all_tables
GROUP BY blocks
HAVING COUNT(*) > 1
ORDER BY 2,1;

INSERT /*+ APPEND */ INTO t
SELECT *
FROM all_tables;

SELECT COUNT(*) FROM t;

------------------------------

exec dbms_errlog.create_error_log('T');

desc err$_t

INSERT /*+ APPEND */ INTO t
SELECT *
FROM all_tables
LOG ERRORS
REJECT LIMIT UNLIMITED
;

SELECT COUNT(*) FROM t;

COMMIT;

SELECT COUNT(*) FROM t;

SELECT COUNT(*) FROM err$_t;

set linesize 121
col table_name format a30
col blocks format a7
col ora_err_mesg$ format a60

SELECT ora_err_mesg$, table_name, blocks
FROM err$_t;
Manually error log creation Oracle makes it possible to manually create an error log table so that you can control the columns that are captured as demonstrated here:

conn uwclass/uwclass@pdbdev

CREATE TABLE error_log_servers (
ora_err_number$ NUMBER,
ora_err_mesg$   VARCHAR2(2000),
ora_err_rowid$  ROWID,
ora_err_optyp$  VARCHAR2(2),
ora_err_tag$    VARCHAR2(2000));

INSERT INTO servers (srvr_id) VALUES (1)
LOG ERRORS INTO ERROR_LOG_SERVERS;

col ora_err_mesg$ format a10
col ora_err_tag$ format a50

SELECT * FROM error_log_servers;

DROP TABLE error_log_servers PURGE;

CREATE TABLE error_log_servers (
ora_err_number$ NUMBER,
ora_err_mesg$   VARCHAR2(2000),
ora_err_rowid$  ROWID,
ora_err_optyp$  VARCHAR2(2),
ora_err_tag$    VARCHAR2(2000),
srvr_id         NUMBER(10));

INSERT INTO servers (srvr_id) VALUES (1)
LOG ERRORS INTO ERROR_LOG_SERVERS;

SELECT * FROM error_log_servers;

It has been suggested in the OTN forums that this capability might be useful, in its most generic form, for creating a single generic error table rather than one table for each base table being accessed for DML.

From my perspective the point of having an error log table is to allow viewing row values, or at least their primary key, to enable a fix. Knowing that an exception took place, but not being able to track it down and do anything about it, is of little or no value. So it is recomended that developers use the default himplementation.

Related Topics
Constraints
Insert Statements
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved