ORACLE - DML Error Logging.

DML Error Logging is a new feature in ORACLE 10gR2.

By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The LOG ERRORS clause in the DML statements enables the operations to complete regardless of errors.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements.

The following types of errors are handled by the error logging clause:

• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints).
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors

The following conditions cause the statement to fail and roll back without invoking the error logging capability:

• Violated deferred constraints
• Out-of-space errors
• Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
• Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation

To do the DML operations with DML error logging:

1. Create an error logging table.
2. Execute the DML statement and include an error logging clause
3. Query the error logging table and take corrective action for the rows that generated errors.

§ Create an error logging table.

The error logging table consists of two parts.

1. A mandatory set of columns that describe the error.

2. An optional set of columns that contain data from the row that caused the error. The column names match the column names from the table being inserted into.

§ Mandatory Error Description Columns.

Column Name	Data Type	Description
ORA_ERR_NUMBER$	NUMBER	Oracle error number
ORA_ERR_MESG$	VARCHAR2(2000)	Oracle error message text
ORA_ERR_ROWID$	ROWID	Rowid of the row in error (for update and delete)
ORA_ERR_OPTYP$	VARCHAR2(2)	Type of operation: insert (I), update (U), delete (D)
ORA_ERR_TAG$	VARCHAR2(2000)	Value of the tag supplied by the user in the error logging clause

Example:

CREATE TABLE EXAM_RESULTS (

STUDENT_ID INTEGER ,
SUBJECT_ID VARCHAR2(5) NOT NULL,
MARKS NUMBER(6) NOT NULL,
REMARKS VARCHAR2(200));

ALTER TABLE EXAM_RESULTS
ADD CONSTRAINT EXAM_RESULTS_PK PRIMARY KEY (STUDENT_ID, SUBJECT_ID);

-- Create the error logging table

-- By using the DBMS_ERRLOG.CREATE_ERROR_LOG() procedure
-- we can automatically create the error logging table
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('EXAM_RESULTS');

PL/SQL procedure successfully completed


SQL> DESCRIBE ERR$_EXAM_RESULTS;
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER Y
ORA_ERR_MESG$ VARCHAR2(2000) Y
ORA_ERR_ROWID$ UROWID(4000) Y
ORA_ERR_OPTYP$ VARCHAR2(2) Y
ORA_ERR_TAG$ VARCHAR2(2000) Y
STUDENT_ID VARCHAR2(4000) Y
SUBJECT_ID VARCHAR2(4000) Y
MARKS VARCHAR2(4000) Y
REMARKS VARCHAR2(4000) Y

Insert records with the DML Error Logging.

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1001, 'CS101', 75, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

1 row inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1002, 'CS101', 80, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

1 row inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1001, 'CS101', 85, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

0 rows inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1002, 'CS101', 90, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

0 rows inserted

In the above example two rows has the problem and they are moved to the error log table. The remaining records are moved to the source table successfully.