dml error logging in oracle 10g release 2
This article introduces DML error logging; a major new feature of Oracle 10g Release 2 for bulk SQL operations. DML error logging enables us to trap "bad data" and filter it to a log table without failing our overall DML statement. This has never been possible in SQL before, although we could use complex constraint management and application code to achieve a slightly similar end-result. DML error logging is more similar in concept to the FORALL SAVE EXCEPTIONS construct in PL/SQL (new in Oracle 9i).
overview of dml error logging
With this feature, we can add a clause to our bulk DML statements (INSERT, UPDATE, MERGE and DELETE) to prevent the statement failing on hitting exceptions (i.e. "bad data"). Exceptional rows are added to a specifically-created errors table for investigation and/or intervention. In addition, we can control the number of bad records we will tolerate before failing the entire statement.
There are two components to DML error logging as follows:
- LOG ERRORS clause to DML statements; and
- DBMS_ERRLOG package for managing error tables.
We shall examine both of these components in this article, but first we will create some sample tables.
getting started: sample data
We will use two tables in our DML error logging examples, as follows. Note that for the examples, I created a user named EL with just CREATE SESSION, CREATE TABLE and a tablespace quota.
SQL> CREATE TABLE src (x,y,z) 2 AS 3 SELECT object_id 4 , object_type 5 , object_name 6 FROM all_objects 7 WHERE ROWNUM <= 5;
Table created.
SQL> CREATE TABLE tgt 2 AS 3 SELECT * 4 FROM src 5 WHERE ROWNUM <= 3;
Table created.
SQL> ALTER TABLE tgt ADD 2 CONSTRAINT pk_tgt 3 PRIMARY KEY (x);
Table altered.
We have a source table (SRC) and a target table (TGT). The data is setup in such a way that a standard INSERT..SELECT from SRC into TGT will fail, as follows.
SQL> INSERT INTO tgt SELECT * FROM src;
INSERT INTO tgt SELECT * FROM src * ERROR at line 1: ORA-00001: unique constraint (EL.PK_TGT) violated
On this basis, we can now introduce the new DML error logging feature. To begin, we will require an exceptions table.
creating the error log table
DML error logging works on the principle of trapping exceptions in bulk SQL statements and re-directing the "bad data" to an error table. The error table is created using an API in the new DBMS_ERRLOG package. The minimum amount of information we need to supply to this is the name of the target table we wish to trap exceptions for. Oracle will by default create an error table named "ERR$_SUBSTR(our_table_name,1,25)". If we so choose, we can optionally control the name, owner and tablespace of the error log table by supplying the relevant parameters.
Given this, we will now create an error log table for TGT and provide a friendly name of our own.
SQL> BEGIN 2 DBMS_ERRLOG.CREATE_ERROR_LOG( 3 dml_table_name => 'TGT', --<-- required 4 err_log_table_name => 'TGT_ERRORS' --<-- optional 5 ); 6 END; 7 /
PL/SQL procedure successfully completed.
SQL> SELECT table_name FROM user_tables;
TABLE_NAME ------------------------------ TGT SRC TGT_ERRORS
The error log table has a number of metadata columns (describing the nature of the exceptional data) and also a VARCHAR2 representation of the base-table columns themselves. The VARCHAR2 columns enable us to see erroneous data that perhaps did not satisfy its base-table datatype. Needless to say, there is a limitation on the datatypes that can be converted to VARCHAR2. For example, ANYDATA, user-defined types and LOBs cannot be captured in the error log table.
The structure of the TGT_ERRORS table as follows.
SQL> desc tgt_errors;
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) X VARCHAR2(4000) Y VARCHAR2(4000) Z VARCHAR2(4000)
invoking dml error logging
Now we have some sample data and an error log table in place, we are ready to see DML error logging in action. We'll begin by replaying the failed INSERT..SELECT from earlier and then describe the new syntax elements.
SQL> INSERT INTO tgt
2 SELECT * FROM src
3 LOG ERRORS INTO tgt_errors ('INSERT..SELECT..RL=UNLIMITED')
4 REJECT LIMIT UNLIMITED;
2 rows created.
Skipping over the fact that our DML statament succeeded for a moment, this is new and extended syntax we haven't seen before. In particular, note the following.
- Line 3: the LOG ERRORS clause is how we invoke DML error logging. We are telling Oracle that we wish our DML statement to succeed in the event that we encounter exceptions below a specified threshold;
- Line 3: the INTO tgt_errors extension to the LOG ERRORS clause is only necessary when using a non-default error log table name, as we are in this article;
- Line 3: the optional literal in brackets enables us to "tag" any bad data that is written to our error table. This helps us to differentiate exceptional data over time. Note this tag can (and should) be a bind variable in "real" applications;
- Line 4: users of external tables will recognise the REJECT LIMIT clause. This is how we specify our threshold for errors (i.e. the number of exceptions we will allow before Oracle fails the entire DML statement).
error log data
Re-visiting our example, therefore, we can see that with DML error logging our insert succeeded but only for 2 rows. We know this is fewer than the number of records in our staging table, so we should check the error log table, as follows (using Tom Kyte's print_table procedure for convenience). Note in particular our tags, which can help us find the bad data quickly on a busy system and also the error message assigned to each failed row (we just happen to have the same exception for each due to the setup of the sample data). In addition, we can see the actual data that we were trying to insert.
SQL> exec print_table( 'SELECT * FROM tgt_errors' );
----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 258 Y : TABLE Z : DUAL ----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 259 Y : SYNONYM Z : DUAL ----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 311 Y : TABLE Z : SYSTEM_PRIVILEGE_MAP PL/SQL procedure successfully completed.
The logged data is not part of the same transaction, which we can demonstrate with a simple rollback. We can see that the error log data is still in the log table. On repeated re-runs and failures, therefore, it will be necessary to tag each statement in such a way as to make then easily identifiable. The tags we've used so far in this article would obviously be useless under such a scenario.
Note that there is a ROWID column in the logging table. This is used when an UPDATE (or update part of a MERGE) fails and provides the ROWID of the target row that was being updated. As we saw with the INSERT example, the "bad data" that caused the exception is recorded in the logging table.
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT COUNT(*) FROM tgt;
COUNT(*)
----------
3
SQL> exec print_table( 'SELECT * FROM tgt_errors' );
----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 258 Y : TABLE Z : DUAL ----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 259 Y : SYNONYM Z : DUAL ----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=UNLIMITED X : 311 Y : TABLE Z : SYSTEM_PRIVILEGE_MAP PL/SQL procedure successfully completed.
reject limit
The default reject limit is 0 (i.e. if this part of the LOG ERRORS clause is omitted). In our first DML error logging example, we used an unlimited reject limit. With this option, a DML statement will succeed even if none of its target operations succeed (i.e. all data is "bad"). If we set an explicit reject limit and exceed it, the entire statement fails but n+1 errors are still logged (where n is the reject limit). We can see this as follows by setting a reject limit of 1. Note that we have changed our tag accordingly to assist with the lookup against the error log.
SQL> INSERT INTO tgt
2 SELECT * FROM src
3 LOG ERRORS INTO tgt_errors ('INSERT..SELECT..RL=1')
4 REJECT LIMIT 1;
INSERT INTO tgt * ERROR at line 1: ORA-00001: unique constraint (EL.PK_TGT) violated
SQL> exec print_table( 'SELECT * FROM tgt_errors WHERE ora_err_tag$ LIKE ''%RL=1%''' );
----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=1 X : 258 Y : TABLE Z : DUAL ----------------- ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (EL.PK_TGT) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..RL=1 X : 259 Y : SYNONYM Z : DUAL PL/SQL procedure successfully completed.
restrictions
Error logging supports all DML operations, including INSERT FIRST|ALL and MERGE. There are some restrictions, however, according to the documentation, that will cause the DML to fail and not invoke error logging at all. These are:
- violated deferred constraints;
- any direct-path INSERT or MERGE operation that raises a unique constraint or index violation; or
- any update operation (UPDATE or MERGE) that raises a unique constraint or index violation.
The second and third of these restrictions are slightly baffling. The second because often in batch environments we are likely to want to combine error logging with direct path loading. The third because it seems to be a pretty standard error in some environments with natural keys (despite all best practice rules about updating PK/UK columns).
We can demonstrate the second restriction quite easily as follows. Note that DML error logging is not invoked at all, despite us adding the LOG ERRORS clause with an unlimited reject limit.
SQL> INSERT /*+ APPEND */ INTO tgt
2 SELECT * FROM src
3 LOG ERRORS INTO tgt_errors ('INSERT..SELECT..DIRECT..ORA-00001')
4 REJECT LIMIT UNLIMITED;
INSERT /*+ APPEND */ INTO tgt * ERROR at line 1: ORA-00001: unique constraint (EL.PK_TGT) violated
SQL> exec print_table( 'SELECT * FROM tgt_errors WHERE ora_err_tag$ LIKE ''%00001%''' );
PL/SQL procedure successfully completed.
Continuing with the same direct-path restriction, we'll remove the primary key and force a different error to show that it will log exceptions other than unique violations. We will try to add too many characters to our Z columns for just one of the rows.
SQL> ALTER TABLE tgt DROP PRIMARY KEY;
Table altered.
SQL> INSERT /*+ APPEND */ INTO tgt
2 SELECT x
3 , y
4 , DECODE(ROWNUM,1,RPAD(z,31,'@'),z) --<-- 31 characters for row 1
5 FROM src
6 LOG ERRORS INTO tgt_errors ('INSERT..SELECT..DIRECT..ORA-12899')
7 REJECT LIMIT UNLIMITED;
4 rows created.
SQL> exec print_table( 'SELECT * FROM tgt_errors WHERE ora_err_tag$ LIKE ''%12899''' );
----------------- ORA_ERR_NUMBER$ : 12899 ORA_ERR_MESG$ : ORA-12899: value too large for column "EL"."TGT"."Z" (actual: 31, maximum: 30) ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..SELECT..DIRECT..ORA-12899 X : 258 Y : TABLE Z : DUAL@@@@@@@@@@@@@@@@@@@@@@@@@@@ PL/SQL procedure successfully completed.
dml error logging in pl/sql
We can see that DML error logging is fully supported in PL/SQL. The SQL%ROWCOUNT attribute will report the successful rowcount only. Unfortunately, there doesn't appear to be an attribute or exception to indicate that errors were logged, so the only option is to examine the error log table itself. In the following example, we will reset our sample data and table and embed our SQL inside a PL/SQL block. We will also use a bind variable for the logging tag.
SQL> ROLLBACK;
Rollback complete.
SQL> ALTER TABLE tgt ADD 2 CONSTRAINT pk_tgt 3 PRIMARY KEY (x);
Table altered.
SQL> DECLARE 2 3 v_unique_tag VARCHAR2(64) := 'INSERT..SELECT..PL/SQL'; 4 5 BEGIN 6 7 INSERT INTO tgt 8 SELECT * FROM src 9 LOG ERRORS INTO tgt_errors (v_unique_tag) 10 REJECT LIMIT 10; 11 12 DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows successfully inserted.' ); 13 14 FOR r IN ( SELECT RTRIM(ora_err_mesg$,CHR(10)) AS err 15 FROM tgt_errors 16 WHERE ora_err_tag$ = v_unique_tag ) 17 LOOP 18 DBMS_OUTPUT.PUT_LINE( r.err ); 19 END LOOP; 20 21 END; 22 /
2 rows successfully inserted. ORA-00001: unique constraint (EL.PK_TGT) violated ORA-00001: unique constraint (EL.PK_TGT) violated ORA-00001: unique constraint (EL.PK_TGT) violated PL/SQL procedure successfully completed.
dropping the error log table
To remove the error log table, we have to manually drop it. Unusually, the DBMS_ERRLOG package does not supply an API for this, but as it is simply a table without any other objects attached to it, we can simply drop it ourselves.
SQL> DROP TABLE tgt PURGE;
Table dropped.
SQL> SELECT table_name FROM user_tables;
TABLE_NAME ------------------------------ SRC TGT_ERRORS
SQL> DROP TABLE tgt_errors PURGE;
Table dropped.
further reading
To read more on the DML error logging clause, including more information on its restrictions, see the Administrator's Guide. To see the performance characteristics of DML error logging and a comparison with the FORALL SAVE EXCEPTIONS clause, read this oracle-developer.net article. The DBMS_ERRLOG package overview can be found in the Supplied Packages and Types Reference.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, August 2005
Back to Top