Oracle例外表学习笔记

文章由LinuxBoy分享于2019-03-30 02:03:51热评（134）

Oracle例外表学习笔记

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操作。

推荐文章：

Oracle例外表学习笔记