DML Error Logging in Oracle 10g Database Release 2

转载

wx6323d657ef2b3 2022-09-16 15:58:24 博主文章分类：Oracle日常管理

Overview
By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. In the past, the only way around this problem was to process each row individually, preferably with a bulk operation using a FORALL loop with the SAVE EXCEPTIONS clause. In Oracle 10g Database Release 2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors. This article presents an overview of the DML error logging functionality, with examples of each type of DML statement.
Syntax

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 the first 25 characters of the base table name are used along with the "ERR$_" prefix.
The simple_expression is used to specify a tag that makes the errors easier to identify. This might be a string or any function whose result is converted to a string.
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.
Restrictions

The DML error logging functionality is not invoked when:
Direct-path INSERT or MERGE operations raise unique constraint or index violations.
UPDATE or MERGE operations raise a unique constraint or index violation.
In addition, the tracking of errors in LONG, LOB and object types is not supported, although a table containing these columns can be the target of error logging.

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.)
Some errors are not logged, and cause the DML operation to terminate and roll back. For a list of these errors and for other DML logging restrictions, see the discussion of the error_logging_clause in the INSERT section of Oracle Database SQL Language Reference.

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF01604

CREATE_ERROR_LOG Procedure
This procedure creates the error logging table needed to use the DML error logging capability.LONG, CLOB, BLOB, BFILE, and ADT datatypes are not supported in the columns.
Syntax
DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            IN VARCHAR2,
   err_log_table_name        IN VARCHAR2 := NULL,
   err_log_table_owner       IN VARCHAR2 := NULL,
   err_log_table_space       IN VARCHAR2 := NULL,
   skip_unsupported          IN BOOLEAN := FALSE);
Table 52-2 CREATE_ERROR_LOG Procedure Parameters

`Parameter`	Description
`dml_table_name`	The name of the DML table to base the error logging table on. The name can be fully qualified ( for example, `emp`, `scott.emp`, `"EMP"`,`"SCOTT"."EMP"`). If a name component is enclosed in double quotes, it will not be upper cased.
`err_log_table_name`	The name of the error logging table you will create.The default is the first 25 characters in the name of the DML table prefixed with `'ERR$_'`. Examples are the following: `dml_table_name`: `'EMP'`, `err_log_table_name`: `'ERR$_EMP'``dml_table_name`: `'"Emp2"'`, `err_log_table_name`: `'ERR$_Emp2'`
`err_log_table_owner`	The name of the owner of the error logging table. You can specify the owner in `dml_table_name`. Otherwise, the schema of the current connected user is used.
`err_log_table_space`	The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.
`skip_unsupported`	When set to `TRUE`, column types that are not supported by error logging will be skipped over and not added to the error logging table.When set to `FALSE`, an unsupported column type will cause the procedure to terminate.The default is false

Error Logging Table Format

The error logging table consists of two parts:

A mandatory set of columns that describe the error. For example, one column contains the Oracle error number.
Table 18-1 lists these error description columns.
An optional set of columns that contain data from the row that caused the error. The column names match the column names from the table being inserted into (the "DML table").The number of columns in this part of the error logging table can be zero, one, or more, up to the number of columns in the DML table. If a column exists in the error logging table that has the same name as a column in the DML table, the corresponding data from the offending row being inserted is written to this error logging table column. If a DML table column does not have a corresponding column in the error logging table, the column is not logged. If the error logging table contains a column with a name that does not match a DML table column, the column is ignored.
Because type conversion errors are one type of error that might occur, the data types of the optional columns in the error logging table must be types that can capture any value without data loss or conversion errors. (If the optional log columns were of the same types as the DML table columns, capturing the problematic data into the log could suffer the same data conversion problem that caused the error.) The database makes a best effort to log a meaningful value for data that causes conversion errors. If a value cannot be derived, NULL is logged for the column. An error on insertion into the error logging table causes the statement to terminate.
Table 18-2 lists the recommended error logging table column data types to use for each data type from the DML table. These recommended data types are used when you create the error logging table automatically with the DBMS_ERRLOG package.

Table 18-1 Mandatory Error Description Columns

Column Name	Data Type	Description
`ORA_ERR_NUMBER$`	`NUMBER`	Oracle error number
`ORA_ERR_MESG$`	`VARCHAR2(2000)`	Oracle error message text
`ORA_ERR_ROWID$`	`ROWID`	Rowid of the row in error (for update and delete)
`ORA_ERR_OPTYP$`	`VARCHAR2(2)`	Type of operation: insert (`I`), update (`U`), delete (`D`)Note: Errors from the update clause and insert clause of a `MERGE` operation are distinguished by the `U` and `I` values.
`ORA_ERR_TAG$`	`VARCHAR2(2000)`	Value of the tag supplied by the user in the error logging clause

Table 18-2 Error Logging Table Column Data Types

DML Table Column Type	Error Logging Table Column Type	Notes
`NUMBER`	`VARCHAR2(4000)`	Able to log conversion errors
`CHAR/VARCHAR2(n)`	`VARCHAR2(4000)`	Logs any value without information loss
`NCHAR/NVARCHAR2(n)`	`NVARCHAR2(4000)`	Logs any value without information loss
`DATE/TIMESTAMP`	`VARCHAR2(4000)`	Logs any value without information loss. Converts to character format with the default date/time format mask
`RAW`	`RAW(2000)`	Logs any value without information loss
`ROWID`	`UROWID`	Logs any rowid type
`LONG/LOB`		Not supported
User-defined types		Not supported

Sample Schema

This following code creates and populates the tables necessary to run the example code in this article.
-- 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)
);
Notice that the CODE column is optional in the SOURCE table and mandatory in the DEST table.
Once the basic tables are in place we can create a table to hold the DML error logs for the DEST. A log table must be created for every base table that requires the DML error logging functionality. This can be done manually or with the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package, as shown below.
-- Create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
END;
/PL/SQL procedure successfully completed.
The owner, name and tablespace of the log table can be specified, but by default it is created in the current schema, in the default tablespace with a name that matches the first 25 characters of the base table with the "ERR$_" prefix.
SELECT owner, table_name, tablespace_name
FROM   all_tables
WHERE  owner = 'TEST';OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           DEST                           USERS
TEST                           DEST_CHILD                     USERS
TEST                           ERR$_DEST                      USERS
TEST                           SOURCE                         USERS4 rows selected.
The structure of the log table includes maximum length and datatype independent versions of all available columns from the base table, as seen below.
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
When we built the sample schema we noted that the CODE column is optional in the SOURCE table, but mandatory in th DEST table. When we populated the SOURCE table we set the code to NULL for two of the rows. If we try to copy the data from the SOURCE table to the DEST table we get the following result.
INSERT INTO dest
SELECT *
FROM   source;SELECT *
       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
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.
INSERT INTO dest
SELECT *
FROM   source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;99998 rows created.
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$ = 'INSERT';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.
Update
The following code attempts to update the CODE column for 10 rows, setting it to itself for 8 rows and to the value NULL for 2 rows.
UPDATE dest
SET    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 expected, the statement fails because the CODE column is mandatory. Adding the DML error logging clause allows us to complete the update of the valid rows.
UPDATE dest
SET    code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE  id BETWEEN 1 AND 10
LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;8 rows updated.
The rows that failed during the update 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$ = 'UPDATE';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.
MergeThe following code deletes some of the rows from the DEST table, then attempts to merge the data from the SOURCE table into the DEST table.
DELETE FROM dest
WHERE  id > 50000;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);
                  *
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
As expected, the merge operation fails and rolls back. Adding the DML error logging clause allows the merge operation to complete.
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.
The rows that failed during the update 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$ = 'MERGE';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.
DeleteThe DEST_CHILD table has a foreign key to the DEST table, so if we add some data to it would would expect an error if we tried to delete the parent rows from the DEST table.
INSERT INTO dest_child (id, dest_id) VALUES (1, 100);
INSERT INTO dest_child (id, dest_id) VALUES (2, 101);
With the child data in place we ca attempt to delete th data from the DEST table.
DELETE FROM dest;
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found
As expected, the delete operation fails. Adding the DML error logging clause allows the delete operation to complete.
DELETE FROM dest
LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;99996 rows deleted.
The rows that failed during the delete operation are stored in the ERR$_DEST table, along with the reason for the failure.
COLUMN ora_err_mesg$ FORMAT A69
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'DELETE';ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
           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 found
2 rows selected.