DML ErrorLogging 

Oracle提供了2类的例外表。 

这是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;

ORA_ERR_NUMBER$

ORA_ERR_MESG$

ORA_ERR_ROWID$

ORA_ERR_OPTYP$

ORA_ERR_TAG$

PKEY

FIELD1

FIELD2

1

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0015362)

I

batch_insert20080922 14:59:50

I 0000005

888888888

x

2290

ORA-02290: 违反检查约束条件 (SCOTT.CHECK_FIELS1_IC)

I

batch_insert20080922 14:59:50

I 077777

888888

x

1400

ORA-01400: 无法将 NULL 插入 ("SCOTT"."DMLEL"."FIELD2")

I

batch_insert20080922 14:59:50

I 077787

88888888

1400

ORA-01400: 无法将 NULL 插入 ("SCOTT"."DMLEL"."FIELD2")

I

batch_insert20080922 14:59:50

I 0000004

88

注意：1、实际上又一条数据有2个字段违反了约束，但日志例外表只反映出一条

     2、如果rollback的话，例外表数据并没有rollback。可以肯定这是一个自主事务。

     3、ora_err_rowid$为null，这是因为这是一个insert操作。

您可能感兴趣的文章: