当你插入几百万数据时，因为有几条脏数据而导致插入失败，是不是非常恼火。Oracle 10g R2之后有个新功能，将插入过程中失败的记录插入到另一张表中。
SQL> drop table test purge;
SQL> drop table test_bad purge;
SQL> create table test as select * from dba_objects where 1<>1;
SQL> execute dbms_errlog.create_error_log('test','test_bad');
create table TEST
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30)
);
create table TEST_BAD
(
ORA_ERR_NUMBER$ NUMBER,
ORA_ERR_MESG$ VARCHAR2(2000),
ORA_ERR_ROWID$ UROWID(4000),
ORA_ERR_OPTYP$ VARCHAR2(2),
ORA_ERR_TAG$ VARCHAR2(2000),
OWNER VARCHAR2(4000),
OBJECT_NAME VARCHAR2(4000),
SUBOBJECT_NAME VARCHAR2(4000),
OBJECT_ID VARCHAR2(4000),
DATA_OBJECT_ID VARCHAR2(4000),
OBJECT_TYPE VARCHAR2(4000),
CREATED VARCHAR2(4000),
LAST_DDL_TIME VARCHAR2(4000),
TIMESTAMP VARCHAR2(4000),
STATUS VARCHAR2(4000),
TEMPORARY VARCHAR2(4000),
GENERATED VARCHAR2(4000),
SECONDARY VARCHAR2(4000),
NAMESPACE VARCHAR2(4000),
EDITION_NAME VARCHAR2(4000)
);
SQL> insert into test(owner) values(lpad('1',31,'aa'))
log errors into test_bad;
insert into test(owner) values(lpad('1',31,'aa'))
*
第 1 行出现错误:
ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大 (实际值: 31, 最大值: 30)
SQL> insert into test(owner) values(lpad('2',30,'bb'))
log errors into test_bad;
已创建 1 行。
SQL> col ORA_ERR_NUMBER format a8;
SQL> col ORA_ERR_MESG$ format a50;
SQL> col OWNER format a20;
SQL> select to_char(ORA_ERR_NUMBER$) as ORA_ERR_NUMBER, ORA_ERR_MESG$, OWNER from test_bad;
ORA_ERR_ ORA_ERR_MESG$ OWNER
-------- -------------------------------------------------- --------------------
12899 ORA-12899: 列 "LCAM_TEST"."TEST"."OWNER" 的值太大 aaaaaaaaaaaaaaaaaaaa
(实际值: 31, 最大值: 30) aaaaaaaaaa1
SQL> select count(1) from test;
COUNT(1)
----------
1
SQL> drop table test1 purge;
SQL> drop table test_bad1 purge;
SQL> create table test1 as select * from dba_objects where 1<>1;
SQL> alter table test1 modify object_id number(2);
SQL> execute dbms_errlog.create_error_log('test1','test_bad1');
SQL> insert into test1 select * from dba_objects
log errors into test_bad1;
insert into test1 select * from dba_objects
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> select count(1) from test1;
COUNT(1)
----------
0
SQL> select count(1) from dba_objects where length(object_id)<=2 or object_id is null;
COUNT(1)
----------
106
--可以看到，上面的语句一有错误就回滚，需要写出下列语句
SQL> insert into test1 select * from dba_objects
log errors into test_bad1
reject limit unlimited;
已创建106行。
SQL> select count(1) from test1;
COUNT(1)
----------
106
SQL> select count(1) from dba_objects where length(object_id)<=2 or object_id is null;
COUNT(1)
----------
106
需要说明的是：