DML Error Logging

DML Error Logging

提问:一个问题， 在存储过程中，有对20万条记录进行修改的一个UPDATE,当中间某个表由于字段长度问题而导致修改失败，在异常处理中，如何定位是哪条记录问题导致失败？

这个无法显示哪行出现超出？你能定位到哪行??

SQL> CREATE OR REPLACE PROCEDURE test111 AS
  2 
  3    v_sqlcode number;
  4    v_sqlerrm varchar2(100);
  5  BEGIN
  6 
  7 
  8  update test set aa=aa||'string' ;
  9      commit;
 10  EXCEPTION
 11      when others then
 12      v_sqlcode:=Sqlcode;
 13      v_sqlerrm:=Sqlerrm;
 14       rollback;
 15  DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'START');
 16 
 17  END;
 18  /
 
Procedure created.
 
SQL> set serveroutput on
SQL> exec test111;
AAA-12899ORA-12899: value too large for column "HR"."TEST"."AA" (actual: 11,
maximum: 10)START

回答: 数据操纵语言错误记录(DML Error Logging)能够满足您的要求

运行SQL语句, 如果只有一条记录出错, 也会立刻终止语句运行, 回滚事务, 导致对数据的修改全部失败, 尤其是当用一条语句批量处理大量记录时这个问题更加突出
10gR2增加了DML错误记录功能, 在运行SQL语句发生某些异常时, 不会中断整个事务, 而是自动将错误信息记录到另一个指定的表, 然后继续处理

1. 举例
创建测试表

set serveroutput on size unlimited
set pages 50000 line 130
drop table t purge;
drop table err$_t purge;
create table t(a number(1) primary key, b char);

然后需要建一个记录错误信息的表, 用Oracle提供的DBMS_ERRLOG包自动创建或手工创建

exec dbms_errlog.create_error_log('t');

缺省名称是ERR$_加原表名的前25个字符

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE
ERR$_T                         TABLE

SQL> desc ERR$_T
 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)
 A                                                                                VARCHAR2(4000)
 B                                                                                VARCHAR2(4000)

SQL>

ORA_ERR_NUMBER$ 错误编号
ORA_ERR_MESG$ 错误信息
ORA_ERR_ROWID$ 出错行的rowid(只对update和delete)
ORA_ERR_OPTYP$ 错误类型 I:插入 U:更新 D:删除
ORA_ERR_TAG$ 由用户定义的标签
(如果采用手工创建, 必须包括上述字段)
后两个字段与原表对应, 数据类型为varchar2(4000), 用于存储出错的记录(数据类型转换见Table 15-2 Error Logging Table Column Data Types)

用原始方式插入测试数据

SQL> insert into t (a) select level from dual connect by level <= 12;
insert into t (a) select level from dual connect by level <= 12
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

字段a只有一位数字, 不能超过9, 插入10导致出错了

增加LOG ERRORS子句后

SQL> insert into t (a) select level from dual connect by level <= 12 log errors reject limit unlimited;

9 rows created.

SQL> select * from t;

         A B
---------- -
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL> col ORA_ERR_MESG$ for a50
SQL> col ORA_ERR_TAG$ for a10
SQL> col ORA_ERR_ROWID$ for a10
SQL> col A for a10
SQL> col b for a10
SQL> select * from err$_t;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                      ORA_ERR_RO OR ORA_ERR_TA A          B
--------------- -------------------------------------------------- ---------- -- ---------- ---------- ----------
           1438 ORA-01438: value larger than specified precision a            I             10
                llowed for this column

           1438 ORA-01438: value larger than specified precision a            I             11
                llowed for this column

           1438 ORA-01438: value larger than specified precision a            I             12
                llowed for this column

SQL语句运行成功并且将错误记录到了err$_t



2. 语法
Error logging的语法是:
LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]

INTO子句可选, 缺省表名是err$_原表名的前25个字符
simple_expression可以是一个由表达式构成字符串, 作为为标签插入到字段ORA_ERR_TAG$
REJECT LIMIT表示最多允许记录多少个错误, 超过这个范围就抛出异常. 如果是0, 表示不记录错误(见下)


3. REJECT LIMIT 子句
在10.2.0.4测试结果和10gR2文档说的有些出入

缺省情况也记录错误, 只记录一条错误

SQL> truncate table t;

Table truncated.

SQL> truncate table err$_t;

Table truncated.

SQL> insert into t (a) select level from dual connect by level <= 13 log errors;
insert into t (a) select level from dual connect by level <= 13 log errors
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * from err$_t;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                          ORA_ERR_RO OR ORA_ERR_TA A
--------------- ---------------------------------------------------------------------- ---------- -- ---------- ----------
B
----------
           1438 ORA-01438: value larger than specified precision allowed for this colu            I             10
                mn



SQL>

如果指定了数量n, 会记录n+1条

SQL> truncate table t;

Table truncated.

SQL> truncate table err$_t;

Table truncated.

SQL> insert into t (a) select level from dual connect by level <= 13 log errors reject limit 2;
insert into t (a) select level from dual connect by level <= 13 log errors reject limit 2
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * from err$_t;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                          ORA_ERR_RO OR ORA_ERR_TA A
--------------- ---------------------------------------------------------------------- ---------- -- ---------- ----------
B
----------
           1438 ORA-01438: value larger than specified precision allowed for this colu            I             10
                mn


           1438 ORA-01438: value larger than specified precision allowed for this colu            I             11
                mn


           1438 ORA-01438: value larger than specified precision allowed for this colu            I             12
                mn



SQL>

11gR1文档似乎修正了这个错误:
This subclause indicates the maximum number of errors that can be encountered before the INSERT statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server.

11gR2文档:
If REJECT LIMIT X had been specified, the statement would have failed with the error message of error X=1. The error message can be different for different reject limits. In the case of a failing statement, only the DML statement is rolled back, not the insertion into the DML error logging table. The error logging table will contain X+1 rows.


4. 错误记录表
错误记录表不会自动清除, 以自治事务运行. 如超过REJECT LIMIT限制, DML语句回滚, 错误记录表不回滚
错误记录表所属的用户和运行DML语句的用户可以不相同, 运行语句的用户对错误记录表必须具有插入权限


5. 使用限制
对以下情况记录错误:

• 列值太大
• 违反非空,唯一,引用(referential),或检查(check)约束条件
• 由触发器抛出的异常
• 数据类型转换错误
• 分区映射错误
• 某些merge操作错误(如 ORA-30926: Unable to get a stable set of rows for MERGE operation.)

下述情况不记录错误:

• 违反了延迟的(deferred)约束条件
• 空间不够
• 直接路径插入操作(insert或merge)抛出的唯一约束或唯一索引错误
• 更新操作(update或merge)抛出的唯一约束或唯一索引错误

外部链接:
DML Error Logging
Error Logging and Handling Mechanisms
Inserting Data with DML Error Logging
38 DBMS_ERRLOG

Faster Batch Processing By Mark Rittman
10gR2 New Feature: DML Error Logging
DML Error Logging in Oracle 10g Database Release 2
Oracle DBMS_ERRLOG
Oracle DML Error Logging
dml error logging in oracle 10g release 2



-fin-