Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, March 6, 2013

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 ''001%''' );

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.

Credit goes to the below website(s):

1 comment: