Have you ever tried to update 30 million records, only to have the update fail after twenty minutes because one record in 30 million fails a check constraint? Or, how about an insert-as-select that fails on row 999 of 1000 because one column value is too large? With DML error logging, adding one clause to your insert statement would cause the 999 correct records to be inserted successfully, and the one bad record to be written out to a table for you to resolve.
In the past, the only way around this problem was to process each row individually, preferably with a bulk operation using FORALLand the SAVE EXCEPTIONS clause
The syntax for the error logging clause is the same for INSERT, UPDATE, MERGE and DELETE statements.
LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]
The optional INTO clause allows you to specify the name of the error logging table. If you omit this clause, the first 25 characters of the base table name are used along with the "ERR$_" prefix.
The REJECT LIMIT is used to specify the maximum number of errors before the statement fails. The default value is 0 and the maximum values is the keyword UNLIMITED. For parallel DML operations, the reject limit is applied to each parallel server.
-- Create a destination table. CREATE TABLE dest ( id NUMBER(10) NOT NULL, code VARCHAR2(10) NOT NULL, description VARCHAR2(50), CONSTRAINT dest_pk PRIMARY KEY (id) ); -- Create the error logging table. BEGIN DBMS_ERRLOG.create_error_log (dml_table_name => 'dest'); END; / PL/SQL procedure successfully completed. The error table gets created with the name that matches the first 25 characters of the base table with the "ERR$_" prefix. SQL> DESC err$_dest 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) ID VARCHAR2(4000) CODE VARCHAR2(4000) DESCRIPTION VARCHAR2(4000)
INSERT INTO dest SELECT * FROM source; SELECT * * ERROR at line 2: ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE") SQL>
The failure causes the whole insert to roll back, regardless of how many rows were inserted successfully. Adding the DML error logging clause allows us to complete the insert of the valid rows.
l_unique_number := i_batch_id || i_chunk_id; ( We will create a unique ID to query the errors associated with the below insert. The Unique ID is stored in the ora_err_tag$ column
INSERT INTO dest SELECT * FROM source LOG ERRORS INTO err$_dest (l_unique_number) REJECT LIMIT UNLIMITED; 99998 rows created. SQL>
The rows that failed during the insert are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A70 SELECT ora_err_number$, ora_err_mesg$ FROM err$_dest WHERE ora_err_tag$ = l_unique_number; ORA_ERR_NUMBER$ ORA_ERR_MESG$ --------------- --------------------------------------------------------- 1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE") 1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE") 2 rows selected.
We can use the unique number to query the error log table and decide whether to rollback or COMMIT.
The same can be dome with Update, delete and Merge
MERGE INTO dest a USING source b ON (a.id = b.id) WHEN MATCHED THEN UPDATE SET a.code= b.code, a.description = b.description WHEN NOT MATCHED THEN INSERT (id, code, description) VALUES (b.id, b.code, b.description) LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED; 99998 rows merged.
DML Error Logging Handles:
- Too-large column values (Except for LONG, LOB, or object type columns)
- Constraint violations (NOT NULL, unique, referential, and check constraints)
- Except for:
- Violated deferred constraints
- Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation
- Any UPDATE or MERGE operation that raises a unique constraint or index violation
- Violation of a constraint on a LONG, LOB, or object type column
- Except for:
- Trigger execution errors
- Type conversion errors arising from type conversion between a column in a subquery and the corresponding column of the table
- Partition mapping errors
- A specific MERGE operation error (ORA-30926: Unable to get a stable set of rows)