实验：例外表的使用
当我们用程序向表中插入大量数据时，可能有几条是不满足条件的，这时候我们可能希望满足条件的数据被执行，而程序不报错。不满足条件的数据呢，可能想把它放到其他地方，以备查验。
oracle为我们提供了一种简单方便的实现方法，通过内部的包即可将例外内容收集，并保证事务正常结束。
我们来看实验：
环境：oracle 10g+windows2003
首先创建了一个测试表：
create table t1 as select object_name,object_type from dba_objects where 1=2;
然后我们给其添加约束：alter table t1 modify object_type not null;
这时，如果直接插入object_type为空的记录就会报错：
SQL> insert into T1 (OBJECT_NAME, OBJECT_TYPE)
2 values ('ICOL$', '');
insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('ICOL$', '')
ORA-01400: 无法将 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE")
下面用dbms_errlog包来捕获例外：
--创建例外捕获表T1_ERROR
begin
dbms_errlog.create_error_log('T1','T1_ERROR','SCOTT');
end;
/
执行成功后我们来插入几条数据：
begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('ICOL$', '') log errors into t1_error reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)
values ('I_USER1', 'TABLE')log errors into t1_error reject limit unlimited;
commit ;
end;
/
可以看到不满足条件的记录被插入到了例外捕获表中：
SQL> select * from t1_error;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ OBJECT_NAME OBJECT_TYPE
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1400 ORA-01400: 无法将 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE") I ICOL$
例外捕获表的结构为：
SQL> desc t1_error;
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
OBJECT_NAME VARCHAR2(4000) Y
OBJECT_TYPE VARCHAR2(4000) Y
以上列值还是比较好理解的，ORA_ERR_NUMBER$是oracle例外代码；ORA_ERR_MESG$是例外提示信息；ORA_ERR_ROWID$是进行更新、删除操作时产生例外的列rowid；ORA_ERR_OPTYP$的取值有I(insert)、U(update)、D(delete);
当执行update语句时，
begin
update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;
commit ;
end;
/
SQL> select * from t1_error t;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ OBJECT_NAME OBJECT_TYPE
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1407 ORA-01407: 无法更新 ("SCOTT"."T1"."OBJECT_TYPE") 为 NULL AAAM24AAEAAAGMkAAC U CON$
ORA_ERR_ROWID$的值对应的是产生例外的待修改的列rowid。
在创建时我们并不需要去指定该表的结构，自动包含了例外代码、例外信息、例外行rowid、例外类型、标志以及所对应表的所有列。
确实很强大。但还不是全部。
试想，如果表的字段比较多，不需要在例外捕获表中重复写入那么多字段，那么只包含有约束的行就行啦，来看实验：
SQL> truncate table t1;
Table truncated
SQL> create table error_show(ora_err_number$ number,ora_err_mesg$ varchar2 (200),object_type varchar2(200));
Table created
SQL>
begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('ICOL$', '') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('I_USER1', 'TABLE')log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('', 'TABLE') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('UNDO$', 'TABLE') log errors into error_show reject limit unlimited;
/*update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;*/
commit ;
end;
ORA-38900: 缺失必需的列 "ORA_ERR_ROWID$" (例外日志表 "ERROR_SHOW")
ORA-06512: 在 line 2
后面有尝试缺少ORA_ERR_NUMBER$列，报错相似。因此得出结论：当使用自定义例外表时，表中还是必须包含oracle指定的列：ORA_ERR_NUMBER$ NUMBER、ORA_ERR_MESG$、ORA_ERR_ROWID$、ORA_ERR_OPTYP$和ORA_ERR_TAG$的，我们可以修改的只是其他的列。仔细思考一下，由于例外表创建时并不知道会产生哪类错误；oracle需要保证所有的例外都被显示出来，而这五列保证了错误显示的完整性。
当然，我们是可以只包含以上这五列的。
SQL> drop table error_show;
Table dropped
SQL> create table error_show(ORA_ERR_NUMBER$ NUMBER,ORA_ERR_MESG$ VARCHAR2(2000),ORA_ERR_ROWID$ UROWID(4000),
ORA_ERR_OPTYP$ VARCHAR2(2),ORA_ERR_TAG$ VARCHAR2(2000));
Table created
执行匿名块：
begin
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('ICOL$', '') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('I_USER1', 'TABLE')log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('', 'TABLE') log errors into error_show reject limit unlimited;
insert into T1 (OBJECT_NAME, OBJECT_TYPE)values ('UNDO$', 'TABLE') log errors into error_show reject limit unlimited;
/*update t1 set object_type='' where object_name='CON$' log errors into t1_error reject limit unlimited;
update t1 set object_type='111' where object_name='UNDO$' log errors into t1_error reject limit unlimited;*/
commit ;
end;
/
SQL>
PL/SQL procedure successfully completed
当只定义了oracle默认的5列作为例外表时，可以将不同表的例外都插入一个例外表中。从ora_err_mesg$列可以区分出是哪个表报错的。
例如，将t表和t1表的例外数据都放入error_show表，查询结果为：
SQL> select * from error_show;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$
--------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- --------------------------------------------------------------------------------
1400 ORA-01400: 无法将 NULL 插入 ("SCOTT"."T1"."OBJECT_TYPE") I
1400 ORA-01400: 无法将 NULL 插入 ("SCOTT"."T"."OBJECT_NAME")
总的来说，例外表的功能还是蛮强大的，值得好好利用，也确实能为我们减轻很多工作负担。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-744994/ ，如需转载，请注明出处，否则将追究法律责任。
