Inserting Data with DML Error Logging:
When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.
DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.
--------------------------------------------------------
-- DDL for Table ATEST1
--------------------------------------------------------
CREATE TABLE "ATOORPU"."ATEST1"
( "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
"TDATE" DATE,
"AMOUNT" VARCHAR2(20 BYTE),
"ORD_NO" NUMBER
) ;
--------------------------------------------------------
INSERT SOME VALUES
--------------------------------------------------------
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);
--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1'); -- ATEST1 source table and ERR_ATEST1 error log table
Error Logging Restrictions and Caveats
- Oracle Database logs the following errors during DML operations:
- 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
Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)
--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------
INSERT INTO ATEST1
SELECT ID+3,TDATE,AMOUNT,ORD_NO
FROM ATEST1
WHERE id > 1
LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;
--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.
Note:
If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------
update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;
--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.
--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------
select * from ERR_ATEST1;
