DML error logging

When a DML error is found in a recent project, it takes about one minute to process more than one thousand pieces of data one by one from a temporary table insert to a formal table. The performance is quite low,
The DML error logging in Oracle 10 Gb has outstanding performance in handling DML exceptions. I originally intended to write an article about this feature. There is just a classic chapter.
Home reference, there are not perfect, please make a brick.
By default, when a DML command fails, the entire statement is rolled back no matter how many records are successfully processed before an error is detected. When using DML Error Log
Previously, the first choice for single-row processing was to use the Save statements of batch SQL forall. In Oracle 10g R2, the DML Error Log feature allows you to solve this problem.
. By adding an appropriate log errors clause for most insert, update, merge, and delete statements, the entire statement can be successfully executed no matter whether an error occurs during the processing.
This article describes the features of DML error logging and provides examples for each situation.

I. Syntax
The same syntax is used 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 this parameter is omitted, the table name of the log will be prefixed with "Err $ _" and added with the base table name.

Simple_expression can be used to specify a flag to facilitate error determination. Simple_expression can be a string or any function that can be converted to a string.

Reject limit is usually used to determine the maximum number of errors allowed by the current statement. The default value is 0, and the maximum value is the keyword unlimited. For parallel DML operations, reject limit
Will be applied to each Parallel Server.

Ii. Restrictions
The following situations invalidate the DML error logging feature
Latency Constraint
Direct-path insert or merge causes violation of unique constraints or unique Indexes
Update or merge causes violation of unique constraints or unique Indexes

In addition, long, lob, and object types are not supported. Even a table containing these columns is used as the target table for error logging.

Iii. Example
The following code creates a table and fills in data for demonstration.

-- Create and populate a source table.                                                                                                                                                      CREATE TABLE source                                                                           (                                                                                                id                  NUMBER( 10 ) NOT NULL                                                    ,code                VARCHAR2( 10 )                                                           ,description         VARCHAR2( 50 )                                                           ,CONSTRAINT source_pk PRIMARY KEY( id )                                                     );                                                                                                                                                                                          DECLARE                                                                                          TYPE t_tab IS TABLE OF source%ROWTYPE;                                                                                                                                                      l_tab               t_tab := t_tab( );                                                     BEGIN                                                                                            FOR i IN 1 .. 100000                                                                          LOOP                                                                                             l_tab.EXTEND;                                                                                 l_tab( l_tab.LAST ).id            := i;                                                       l_tab( l_tab.LAST ).code          := TO_CHAR( i );                                            l_tab( l_tab.LAST ).description   := 'Description for ' || TO_CHAR( i );                   END LOOP;                                                                                                                                                                                   -- For a possible error condition.                                                            l_tab( 1000 ).code    := NULL;                                                                l_tab( 10000 ).code   := NULL;                                                                                                                                                              FORALL i IN l_tab.FIRST .. l_tab.LAST                                                            INSERT INTO source                                                                            VALUES l_tab( i );                                                                                                                                                                       COMMIT;                                                                                    END;                                                                                          /                                                                                                                                                                                           EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);                          -- 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 a dependant of the destination table.                                                                                                                                             CREATE TABLE dest_child                                                                       (                                                                                                id                  NUMBER                                                                   ,dest_id             NUMBER                                                                   ,CONSTRAINT child_pk PRIMARY KEY( id )                                                        ,CONSTRAINT dest_child_dest_fk FOREIGN KEY( dest_id ) REFERENCES dest( id )                 );                                                                                            

Note: The code column is optional in the source table, while the code column is mandatory in the Dest table.

After a base table is created, if you need to use the DML error logging feature, you must create a log table for the base table to record DML Errors Based on the table. The error log table can
Manually create or use the create_error_log stored procedure in the package. As follows:

-- Create the error logging table. begin dbms_errlog.create_error_log (dml_table_name => 'dest'); end;/PL/SQL procedure successfully completed. -- by default, the created log table is based on the current schema. The owner of the log table and the log name. The tablespace name can also be specified separately. The default log table name is based on the base table and starts with the prefix -- "Err $. Select owner, table_name, tablespace_namefrom all_tableswhere owner = 'test'; Owner table_name tablespace_name should have prior test DEST userstest dest_child userstest err $ _ DEST userstest source users4 rows selected. -- The structure, data type, and maximum length of the log table depend on the base table, as shown in the following figure: SQL> DESC err $ _ DEST name null? Type reserved -------- -------------- ora_err_number $ number Reserved $ varchar2 (2000) ora_err_rowid $ rowid Reserved $ varchar2 (2) ora_err_tag $ varchar2 (2000) ID varchar2 (4000) code varchar2 (4000) description varchar2 (4000)

1. insert operation
When creating a demo table, the code column of the source table can be null, while the code column of the Dest table cannot be null. When filling the source table, two null records are set.
If we try to copy data from the source table to the Dest entry, the following error message will be obtained:

Insert into destselect * from source; select ** error at line 2: ORA-01400: cannot insert null into ("test ". "DEST ". "Code") -- the two rows whose source table is null cause the entire insert statement to roll back, no matter how many statements are inserted between errors. By adding the DML error logging clause, we can successfully insert valid data. Insert into destselect * From sourcelog errors into err $ _ DEST ('insert') reject limit unlimited; 99998 rows created. -- records that fail to be inserted will be recorded in err $ _ DEST, and the cause of the error is also recorded. Column sequence $ format a70select ora_err_number $, sequence $ from err $ _ destwhere ora_err_tag $ = 'insert'; ora_err_number $ ora_err_mesg $ ------------- analyze 1400 ORA-01400: cannot insert null into ("test ". "DEST ". "Code") 1400 ORA-01400: cannot insert null into ("test ". "DEST ". "Code") 2 rows selected.

2. Update operation
The following code attempts to update the code columns of rows 1-10. The code values of eight rows are set to itself, while those of rows 9th and 10th are set to null.

Update destset code = decode (ID, 9, null, 10, null, code) where ID between 1 and 10; * error at line 2: ORA-01407: cannot update ("test ". "DEST ". "Code") to null -- as we expected, the operation fails because the code column cannot be null. Similarly, by adding the DML erorr logging clause, we can complete the operation of valid records update destset code = decode (ID, 9, null, 10, null, code) where ID between 1 and 10log errors into err $ _ DEST ('update') reject limit unlimited; 8 rows updated. -- Similarly, rows that fail the update operation and the cause of failure are recorded in the err $ _ DEST table column ora_err_mesg $ format a70select ora_err_number $, ora_err_mesg $ from err $ _ destwhere ora_err_tag $ = 'update'; ora_err_number $ ora_err_mesg $ ------------- limit 1400 ORA-01400: cannot insert null into ("test ". "DEST ". "Code") 1400 ORA-01400: cannot insert null into ("test ". "DEST ". "Code ")

3. Merge operations
The following code deletes some rows from the Dest table and then tries to merge data from the source table to the Dest table.

Delete from destwhere ID> 50000; merge into dest a using source B on (. id = B. ID) when matched then update set. code = B. code,. description = B. description when not matched then insert (ID, code, description) values (B. ID, B. code, B. description); * error at line 9: ORA-01400: cannot insert null into ("test ". "DEST ". "Code") -- The merge operation also fails and rolls back because of the not null constraint. -- Add DML error logging to allow merge to complete merge into dest a using source B on (. id = B. ID) when matched then update set. code = B. code,. 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. -- The failed row Update and the cause of failure are also recorded in the column ora_err_mesg $ format a70select ora_err_number $, ora_err_mesg $ from err $ _ destwhere ora_err_tag $ = 'merge' in the err $ _ DEST table '; 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.

4. delete operation
The dest_child table has a foreign key constraint to the Dest table. Therefore, if we add some data to the dest_child table based on the Dest table, then deleting records from the Dest table will produce an error.

Insert into dest_child (ID, dest_id) values (1,100); insert into dest_child (ID, dest_id) values (2,101); Delete from DEST; * error at line 1: ORA-02292: integrity Constraint (test. dest_child_dest_fk) violated-Child record found -- for delete operations, you can also add the DML error logging clause to record errors so that the entire statement is successfully executed. Delete from destlog errors into err $ _ DEST ('delete') reject limit unlimited; 99996 rows deleted. -- The following are the logs of the delete operation failure and the error cause. Column comment $ format a69select ora_err_number $, ora_err_mesg $ from err $ _ destwhere ora_err_tag $ = 'delete'; ora_err_number $ comment $ ------------- limit 2292 ORA-02292: Integrity Constraint (test. dest_child_dest_fk) violated-Child record found 2292 ORA-02292: Integrity Constraint (test. dest_child_dest_fk) violated-Child record found2 rows selected.

Iv. Postscript
1. the DML error logging feature uses autonomous transactions. Therefore, no matter whether the current primary transaction is committed or rolled back, the error information generated by the primary transaction is recorded in the corresponding log table.
2. DML error logging enables efficient error processing. Even so, if many tables require DML operations, especially during data migration
Create a log table. A test is conducted to delete some base table columns in the log table and retain the primary columns. Logs can still be successfully recorded to reduce the log size.
3. Can I Merge multiple log tables into one log table, and add corresponding table names and primary keys to each row of data to identify errors? In this case, only a small amount of logs are used.
You can record DML errors on multiple tables. This is a question.

5. Example of using the Save conditions clause of forall

Forall: An Example of saving exceptions clause