当一个DML运行的时候,如果遇到了错误,则这条语句会整个回滚,就好像没有执行过。不过对于一个大的DML而言,如果个别数据错误而导致整个语句的回滚,会浪费很多的资源和运行时间,从10g开始Oracle支持记录DML语句的错误,而允许语句自动继续执行。
这篇介绍DML记录语句不支持的数据类型。
Oracle10g新特性——记录DML错误日志(一):http://yangtingkun.itpub.net/post/468/479317
Oracle10g新特性——记录DML错误日志(二):http://yangtingkun.itpub.net/post/468/479366
除了上一篇文章介绍的不支持的操作外,DML记录错误语句还有一些不支持的数据类型,比如:LONG、LONG RAW、BLOG、CLOB、NCLOB、BFILE以及各种对象类型。
Oracle不支持这些类型的原因也很简单,这些特殊的类型不是包含了大量的记录,就是需要通过特殊的方法来读取,因此Oracle没有办法在SQL处理的时候将对应列的信息写到错误记录表中。
SQL> DROP TABLE T PURGE;
表已删除。
SQL> DROP TABLE T_ERROR_LOG PURGE;
表已删除。
SQL> CREATE TABLE T
2 (ID NUMBER,
3 NAME VARCHAR2(30),
4 RESUME CLOB);
表已创建。
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG')
BEGIN DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG'); END;
*第 1 行出现错误:
ORA-20069: Unsupported column type(s) found: RESUME
ORA-06512: 在 "SYS.DBMS_ERRLOG", line 233
ORA-06512: 在 line 1
可以看到,由于T表拥有不支持的列,导致创建错误记录表的过程报错,错误提示就是T表中包含了不支持的列。
如果手工添加CLOB字段到错误记录表:
SQL> ALTER TABLE T DROP (RESUME);
表已更改。
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T', 'T_ERROR_LOG')
PL/SQL 过程已成功完成。
SQL> ALTER TABLE T ADD RESUME CLOB;
表已更改。
SQL> ALTER TABLE T_ERROR_LOG ADD RESUME CLOB;
表已更改。
下面创始执行DML语句:
SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
2 LOG ERRORS INTO T_ERROR_LOG
3 REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
*第 2 行出现错误:
ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录
SQL> INSERT INTO T VALUES (1, 'A', 'TEST')
2 LOG ERRORS INTO T_ERROR_LOG
3 REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
*第 2 行出现错误:
ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录
可以看到,Oracle会直接报错。而且第二个INSERT语句的执行也会报错,说明Oracle是在执行之前检查了错误记录表的数据类型,而不是在执行的时候才去处理。
即使Oracle的DML并不包含不支持列的数据,Oracle也会报错:
SQL> INSERT INTO T VALUES (1, 'A', 'TEST');
已创建 1 行。
SQL> UPDATE T SET NAME = 'B'
2 WHERE ID = 1
3 LOG ERRORS INTO T_ERROR_LOG
4 REJECT LIMIT 1;
LOG ERRORS INTO T_ERROR_LOG
*第 3 行出现错误:
ORA-38904: LOB 列 "RESUME" 不支持 DML 错误事件记录
下面创始删除T_ERROR_LOG表中的RESUME列:
SQL> ALTER TABLE T_ERROR_LOG DROP (RESUME);
表已更改。
SQL> UPDATE T SET NAME = 'B'
2 WHERE ID = 1
3 LOG ERRORS INTO T_ERROR_LOG
4 REJECT LIMIT 1;
已更新 1 行。
SQL> UPDATE T SET NAME = LPAD('A', 31, 'A')
2 WHERE ID = 1
3 LOG ERRORS INTO T_ERROR_LOG
4 REJECT LIMIT 1;
已更新0行。
SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
2 LOG ERRORS INTO T_ERROR_LOG
3 REJECT LIMIT 1;
已创建0行。
SQL> SELECT COUNT(*) FROM T_ERROR_LOG;
COUNT(*)
----------
2
可以看到,删除错误记录语句所不支持的列后,LOG ERRORS语句反而可以顺利执行,而且无论DML语句是否包括哪些不支持列的数据。
不过,对于一个已经在使用的表,是不可能为了添加错误记录表而删除不支持的列,而后在添加回来的,这没有关系,完全可以通过其他表中间表来实现:
SQL> DROP TABLE T_ERROR_LOG PURGE;
表已删除。
SQL> CREATE TABLE T_INTER AS SELECT ID, NAME FROM T;
表已创建。
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T_INTER', 'T_ERROR_LOG')
PL/SQL 过程已成功完成。
SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
2 LOG ERRORS INTO T_ERROR_LOG
3 REJECT LIMIT 1;
已创建0行。
可以看到,虽然CREATE_ERROR_LOG过程在创建T_ERROR_LOG表时指定的是T_INTER表,但是这个过程实际上只是根据源表来获取列的信息,而并没有在源表和错误记录表之间建立任何的关系,所以只要列满足条件,任何途径创建的错误记录表都可以使用。
错误记录表还可以手工创建:
SQL> CREATE TABLE T_ERROR_LOG1 AS SELECT * FROM T_ERROR_LOG
2 WHERE 1 = 0;
表已创建。
SQL> DROP TABLE T_ERROR_LOG PURGE;
表已删除。
SQL> INSERT INTO T VALUES (1, LPAD('A', 31, 'A'), 'TEST')
2 LOG ERRORS INTO T_ERROR_LOG1
3 REJECT LIMIT 1;
已创建0行。