DML ErrorLogging
这是10gR2以后版本才有的新特征
DML error logging 是ORACLE10gR2版本中的新特征。这个新的特征是解决如下的问题：假定你要向一个目标表更新或者插入大批数据，过程已经运行了很长时间，就在程序快要结束前，一条违反约束的情况发生了，这个事务也会失败。前面花费很长时间运行的工作作废了不算，还要花费很长时间做ROLLBACK处理，这种情况让人恼怒不已。有DML error logging这个特性，可以避免上述情况，当遇到违反约束的情况下，ORACLE会将该条违反约束的数据记录在另外一张表中，程序继续运行。在整个程序运行完后，可以手动或者编写一段代码，对违反约束的数据进行单独处理。
本节介绍如何INSERT, UPDATE, MERGE 和DELETE如何使用DML ERROR LOGGING。
建立一张表
create table dmlel
(
pkey varchar2(100) primary key,
field1 integer,
field2 varchar2(10) not null
);
alter table dmlel add constraint check_fiels1_ic
CHECK(regexp_like(field1,'^[0-9]{8,10}$'));
这张表存在3个约束。
现在编写一个脚本，在这个脚本中没有采用DML error logging
假定有一张元数据表，我们要将该表的数据插入
drop table dmlel_origin purge;
create table dmlel_origin
(
pkey varchar2(10),
field1 integer,
field2 varchar2(10)
);
begin
for i in 1..10000 loop
insert intodmlel_origin(pkey,field1,field2)
values('I'||to_char(i, '0000000'),trunc(dbms_random.value(10000000,999999999)),'x');
end loop;
commit;
end;
select * from dmlel_origin
我们增加几条违背约束的数据
insert intodmlel_origin(pkey,field1,field2)
values('I 0000005',888888888,'x');
insert intodmlel_origin(pkey,field1,field2)
values('I 077777',888888,'x');
insert intodmlel_origin(pkey,field1,field2)
values('I 077787',88888888,null);
insert intodmlel_origin(pkey,field1,field2)
values('I 0000004',88,null);
自动建立日志例外表
declare
begin
-- Test statements here
DBMS_ERRLOG.create_error_log('DMLEL','DMLEL_LOG','SCOTT');
end;
desc DMLEL_LOG;
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER Y
ORA_ERR_MESG$ VARCHAR2(2000) Y
ORA_ERR_ROWID$ UROWID(4000) Y
ORA_ERR_OPTYP$ VARCHAR2(2) Y
ORA_ERR_TAG$ VARCHAR2(2000) Y
PKEY VARCHAR2(4000) Y
FIELD1 VARCHAR2(4000) Y
FIELD2 VARCHAR2(4000) Y
insert /*+append*/ into DMLEL
select * from dmlel_origin
ORA-02290: 违反检查约束条件 (SCOTT.CHECK_FIELS1_IC)
truncate table DMLEL
truncate table dmlel_log
insert into DMLEL
select * from dmlel_origin
log errors into dmlel_log('batch_insert'||to_char(sysdate,'yyyymmddhh24:mi:ss')) reject limit unlimited;
注意：1、实际上又一条数据有2个字段违反了约束，但日志例外表只反映出一条
2、如果rollback的话，例外表数据并没有rollback。可以肯定这是一个自主事务。
3、ora_err_rowid$为null，这是因为这是一个insert操作。