oracle几种例外,Oracle例外表学习笔记

于 2021-04-11 09:57:56 发布
阅读量323 收藏
点赞数
文章标签： oracle几种例外
本文详细介绍了Oracle数据库从10gR2版本引入的DML Error Logging特性，该特性允许在遇到违反约束条件时，将错误数据记录到专门的日志表中，而不是导致整个事务回滚。通过示例展示了如何在INSERT, UPDATE, MERGE和DELETE操作中使用DML Error Logging，以及如何手动创建和定制错误日志表来优化空间使用和错误处理。此外，还讨论了错误日志表如何捕获触发器和类型转换错误，并提供了处理违反约束和触发器错误的示例代码。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

The original websit:http://blog.youkuaiyun.com/tiwen818/article/details/7166532

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

你也可以自己手工创建日志例外表，手工创建可以更加个性化地处理违反约束的规则，如果有一张表，有很多的字段，有一些字段上虽然存在约束，但不是关键性的约束，如果采用自动建立日志例外表的方法，这样这张表就可能有很多的字段，这张表占用了更多地空间，这种情况下，就可以自定义日志例外表。

drop table DMLEL_LOG_1 purge

CREATE TABLE DMLEL_LOG_1

(

--ORA_ERR_NUMBER$ NUMBER,

ORA_ERR_MESG$VARCHAR2(2000),

ORA_ERR_ROWID$ UROWID (4000),

ORA_ERR_OPTYP$ VARCHAR2(2),

ORA_ERR_TAG$ VARCHAR2(2000),

PKEY VARCHAR2(4000),

FIELD1 VARCHAR2(4000)--,

--FIELD2 VARCHAR2(4000)

)

truncate table DMLEL;

truncate table dmlel_log;

insert  into DMLEL

select * from dmlel_origin

log errors into dmlel_log_1('batch_insert'||to_char(sysdate,'yyyymmddhh24:mi:ss')) reject limit unlimited

ORA-38900: 缺失必需的列 "ORA_ERR_NUMBER$" (错误日志表"DMLEL_LOG_1")

drop table DMLEL_LOG_1 purge

CREATE TABLE DMLEL_LOG_1

(

ORA_ERR_NUMBER$ NUMBER,

ORA_ERR_MESG$VARCHAR2(2000),

ORA_ERR_ROWID$ UROWID (4000),

ORA_ERR_OPTYP$ VARCHAR2(2),

ORA_ERR_TAG$ VARCHAR2(2000),

PKEY VARCHAR2(4000),

FIELD1 VARCHAR2(4000)--,

--FIELD2 VARCHAR2(4000)

)

truncate table DMLEL;

truncate table dmlel_log_1;

insert  into DMLEL

select * from dmlel_origin

log errors into dmlel_log_1('batch_insert'||to_char(sysdate,'yyyymmddhh24:mi:ss')) reject limit unlimited

select * from DMLEL_LOG_1

ORA_ERR_NUMBER$

ORA_ERR_MESG$

ORA_ERR_ROWID$

ORA_ERR_OPTYP$

ORA_ERR_TAG$

PKEY

FIELD1

1

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

I

batch_insert20080922 14:59:50

I 0000005

888888888

2290

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

I

batch_insert20080922 14:59:50

I 077777

888888

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

Update DMLEL set field1=123

Where pkey= 'I 0000007'

log errors into dmlel_log_1('update'||to_char(sysdate,'yyyymmddhh24:mi:ss')) reject limit unlimited;

selectt.ora_err_mesg$,t.ora_err_rowid$,t.ora_err_optyp$,t.ora_err_tag$ from dmlel_log_1 t

ORA_ERR_MESG$

ORA_ERR_ROWID$

ORA_

ERR_

OPTYP$

ORA_ERR_TAG$

ORA-00001: 违反唯一约束条件

(SCOTT.SYS_C0015362)

I

batch_insert20080922 15:15:23

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

I

batch_insert20080922 15:15:23

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

I

batch_insert20080922 15:15:23

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

I

batch_insert20080922 15:15:23

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

AAAT+gAAEAABh+EAAG

U

update20080922 15:22:47

为了用DML ErrorLogging处理上述错误，首先建立一个错误日志表。

建立ErrorLogging Table

有2种方法建立Error Logging表，一个方法是手工创建，另一种方法自动创建。以下是自动创建的语法：

begin

dbms_errlog.create_error_log('DMLEL','ERROR_LOG_DMLEL');

end;

这张表有以下结构：

desc error_log_dmlel

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)

PKEY                                              VARCHAR2(4000)

FIELD1                                            VARCHAR2(4000)

FIELD2                                            VARCHAR2(4000)

ORA_ERR_TAG$列标识出当前处理的信息。

DML日志例外表能够处理的错误信息，除了约束外，还可以标识触发器的错误信息，还有类型转化时候发生的错误。

CREATE OR REPLACE TRIGGER update_field2

BEFORE UPDATE OF field2 ON dmlel FOR EACH ROW

DECLARE

BEGIN

if :old.field2 <= :new.field2 then

raise_application_error(-20105, '新值必须小于旧值');

end if;

END;

Update DMLEL set field2='y'

Where pkey= 'I 0000002'

ORA-20105: 新值必须小于旧值

ORA-06512: 在"SCOTT.UPDATE_FIELD2", line 4

ORA-04088: 触发器'SCOTT.UPDATE_FIELD2' 执行过程中出错

truncate table dmlel_log_1;

Update DMLEL set field2='y'

Where pkey= 'I 0000002'

log errors into dmlel_log_1('update'||to_char(sysdate,'yyyymmddhh24:mi:ss')) reject limit unlimited;

ORA_ERR_NUMBER$

20105

ORA_ERR_MESG$

ORA-20105: 新值必须小于旧值

ORA-06512: 在 "SCOTT.UPDATE_FIELD2", line 4

ORA-04088: 触发器 'SCOTT.UPDATE_FIELD2' 执行过程中出错

ORA_ERR_ROWID$

AAAT+gAAEAABh+EAAB

ORA_ERR_OPTYP$

U

ORA_ERR_TAG$

update20080922 17:59:51

PKEY

I 0000002

FIELD1

63370697

第二种形式的例外表，其格式为

create table excep_check_field1(

row_id rowid,

owner varchar2(30),

table_name varchar2(30),

constraint varchar2(30)

);

excep_check_field1为例外表的名称

我们在dmlel_origin故意插入2条违反约束

regexp_like(field1,'^[0-9]{8,10}$')

的2条数据；

insert intodmlel_origin(pkey,field1,field2)

values('I 077777',888888,'x');

insert intodmlel_origin(pkey,field1,field2)

values('I 0000004',88,null);

我们运行以下脚本，找出这2条数据：

truncate table excep_check_field1;

alter table dmlel_origin add constraintcheck_fiels1_ic

CHECK( regexp_like(field1,'^[0-9]{8,10}$'))

enable validate exceptions into exceptions

ORA-02264: 名称已被一现有约束条件占用

上面过程虽然没有成功，但是在例外表中记录了违反constraintcheck_fiels1_ic的2条数据；

select * from excep_check_field1

ROW_ID

OWNER

TABLE_NAME

CONSTRAINT

AAAT+YAAEAABh99AEk

SCOTT

DMLEL_ORIGIN

CHECK_FIELS1_IC_1

AAAT+YAAEAABh99AEm

SCOTT

DMLEL_ORIGIN

CHECK_FIELS1_IC_1

Select field1 from dmlel_origin where rowid in(select row_id from excep_check_field1)

删除修改违反约束的数据，再添加约束

Delete from dmlel_origin

where rowid in(select row_id from excep_check_field1)

truncate table excep_check_field1;

alter table dmlel_origin add constraint check_fiels1_ic_1

CHECK(regexp_like(field1,'^[0-9]{8,10}$'))

enable validate exceptions into excep_check_field1;

Table altered

这一次，表被成功修改。

Playmz
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值