获取导致导入失败的数据（二）

前不久从一个数据库执行导出操作时报错，通过直接路径方式跳过后，导入时候再次报错。推测是由于源数据库出现的异常导致表中数据超过表定义的精度。

由于源数据库中错误记录已经被删除，因此只能想办法从导出的dmp文件中获取错误的记录。

导出、导入过程的描述可以参考：

EXP在9R2上导出时报错ORA-3113和ORA-24324：http://yangtingkun.itpub.net/post/468/460647

EXP在9R2上导出时报错ORA-3113和ORA-24324（二）：http://yangtingkun.itpub.net/post/468/460831

上一篇通过触发器的方式没有获取到具体的错误数据，这篇继续修正方法继续找出异常的数据：

获取导致导入失败的数据：http://yangtingkun.itpub.net/post/468/461401

BEFORE触发器修正数据错误：http://yangtingkun.itpub.net/post/468/461506

由于上一篇的诸多测试已经将各个表的结构修改的比较混乱，下面重建所有的测试结构：

SQL> DROP TABLE SHGOV_ORDER_BAK;

表已丢弃。

SQL> DROP TABLE SHGOV_ORDER;

表已丢弃。

SQL> CONN / AS SYSDBAITPUB个人空间f|+u'_ ?s
已连接。
9z!O1lx^l:z3?,f0SQL> DROP TRIGGER FIND_ERR_SQL;

触发器已丢弃

SQL> DROP TABLE T_LOG;

表已丢弃。

利用导入工具重建SHGOV_ORDER表：

[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y rows=n

Import: Release 9.2.0.4.0 - Production on星期五5月9 17:48:37 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ITPUB个人空间1UQ3qa}
N
连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

L
Eq)L#vg(T0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间w/g)n,I_0a$CZJ
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件

警告:此对象由SHGOV导出,而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入
X,F`6OXA{P*r0.正在将SHGOV的对象导入到TEST
l$W'Ic@jP0IMP-00009:导出文件异常结束
bjId,|/@K$i
l0成功终止导入，但出现警告。

下面建立用来存储触发器插入记录的表：

SQL> CREATE TABLE SHGOV_ORDER_BAK ASITPUB个人空间&{P-|@\g)Ht;\
  2  SELECT TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,ITPUB个人空间4q'FT:N+Y
  3     UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
\k4@W{k0  4  FROM SHGOV_ORDER;

表已创建。

SQL> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);

表已创建。

怀疑问题出在NUMBER类型的字段上，因此目标表只包含了NUMBER类型的字段，而且为了方便查询，将系统触发器的日志也放到当前用户下。

由于采用静态SQL的方式，从系统触发器获取的SQL语句都是绑定变量的形式，没有办法看到引发错误的数据，因此触发器修改一下，改为动态SQL的方式，希望采用这种方式可以获取到错误的数据。

SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQLITPUB个人空间 oGd{!P
  2  BEFORE INSERT ON SHGOV_ORDERITPUB个人空间Cc`6~A
p~\4M
  3  FOR EACH ROW
a3ho0L!NH0  4  DECLARE
Bm'L)X%p8[2tn"X"J1i0  5   PRAGMA AUTONOMOUS_TRANSACTION;ITPUB个人空间*qkt]N+I![
  6  BEGIN
'N
h0W2CWRk5BL0  7   EXECUTE IMMEDIATE 'INSERT INTO SHGOV_ORDER_BAK
.`AYA"O)u0  8   (
]hc!ZWO[0nl@ A#G"o0  9    TRADE_RATE, ORDER_AMOUONT, SEND_AMOUNT, RECEIVE_AMOUNT,
l3\-J*F*\,Q0 10    UNIT_PRICE, MAX_PRICE, MIN_PRICE, PRICE_RATE
;U'\s+jdB0 11   )
h
x-|7{W9Z.{(a(J6U5`_0 12   VALUES
&{sbM;X+},[9a!\0 13   (
4|*w0x'}v#^\0 14    ' || :NEW.TRADE_RATE || ', ' || :NEW.ORDER_AMOUONT || ', '
x!{*eO iGKNu0 15    || :NEW.SEND_AMOUNT || ', ' || :NEW.RECEIVE_AMOUNT || ', '
:X-P]H7|"[0 16    || :NEW.UNIT_PRICE || ', ' || :NEW.MAX_PRICE || ', '
2t[1H6w:o7t:X#Z"w0 17    || :NEW.MIN_PRICE || ', ' || :NEW.PRICE_RATE || ')';
!B;\'s:O7f:[:c J0 18   COMMIT;ITPUB个人空间Xhab_S
 19  END;ITPUB个人空间/i6XYC4B u$y?
 20  /

ITPUB个人空间:rm%Ka Pu-\-z:gL
触发器已创建

最后建立系统触发器，并对原有代码进行简单的调整，去掉了对1438错误的判断，这样发生任何的错误都可以捕获。

SQL> CONN / AS SYSDBAITPUB个人空间#PR5nA |.W x6R
已连接。ITPUB个人空间9c:?5{!M`:V(y
SQL> CREATE OR REPLACE TRIGGER FIND_ERR_SQL AFTER SERVERERROR ON DATABASE
\bH@0V7b0  2  DECLARE
*~:pw-_H&zk0  3     V_SQL_OUT ORA_NAME_LIST_T;
[pQ c#TAw|Z0  4     V_NUM NUMBER;ITPUB个人空间)o1F+[b;N n:_
  5     V_SQL_STATMENT VARCHAR2(32767);ITPUB个人空间'R2y0fGOA?
  6  BEGIN
jnZ8tX.L0  7             V_NUM := ORA_SQL_TXT(V_SQL_OUT);ITPUB个人空间(s(AD#i'ipvR*bp
  8             FOR I IN 1 .. V_NUM LOOPITPUB个人空间!h@wH2GZ7]ch
  9                     V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);ITPUB个人空间oxYt
bmz7U
 10             ENDLOOP;
DSz$@
v5}I0 11             INSERT INTO TEST.T_LOG (EXECUTE_DATE, SQL_STATMENT)
NT4d OVsN0 12             VALUES (SYSDATE, V_SQL_STATMENT);
Z[7L,{q/v}f M0 13  END;
|@#e5`7hA~0 14  /

触发器已创建

由于IMP采用绑定变量的方式，捕获IMP的错误没有意义，而且由于Oracle会先验证插入数据的合法性，然后调用BEFORE触发器，因此导入错误记录时，BEFORE触发器是不会被触发的。所以，这里需要修改一下SHGOV_ORDER表的数值类型，将其转化为NUMBER类型，这样让精度问题出现在触发器中，使得系统触发器可以捕获这个错误。

SQL> ALTER TABLE SHGOV_ORDER MODIFY
K
s.v?F7K2s9p9Cr0  2  (

eg|$I!kK:e'wxmo0  3     TRADE_RATE NUMBER,
(V{p(qc'S4z8Z0  4     UNIT_PRICE NUMBER,
_'?x"I jx:Fs~5o2W0  5     MAX_PRICE NUMBER,ITPUB个人空间_ba0us No;Ik
  6     MIN_PRICE NUMBER,ITPUB个人空间d`zVcp
A
  7     PRICE_RATE NUMBER
.B p ] a;Z0pN0  8  );

表已更改。

下面执行导入：

[oracle@bjtest data]$ imp test/test file=jg080424.dmp tables=shgov_order buffer=2048000 ignore=y

Import: Release 9.2.0.4.0 - Production on星期五5月9 18:15:08 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

#KU8P4cheqC|.y!H
M] g0连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
^V~v&C+jP-}!~0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间Vz~F(\
JServer Release 9.2.0.4.0 - Production

经由常规路径导出由EXPORT:V09.02.00创建的文件

警告:此对象由SHGOV导出,而不是当前用户

已经完成ZHS16GBK字符集和AL16UTF16 NCHAR字符集中的导入ITPUB个人空间%b@RTL4U7OD
.正在将SHGOV的对象导入到TESTITPUB个人空间U%a2a(P!R2e#N
. .正在导入表                   "SHGOV_ORDER"ITPUB个人空间
M.E`2x$V!n
IMP-00058:遇到ORACLE错误917ITPUB个人空间[%f.kd?dY9Y/A
ORA-00917:缺少逗号
{,@r z,f Q+v`0ORA-06512:在"TEST.FIND_ERR_SQL", line 4
iO.oy/u?0ORA-04088:触发器'TEST.FIND_ERR_SQL'执行过程中出错
&n7PE${%eq0IMP-00028:上一个表的部分导入已回退:回退31322行ITPUB个人空间C:R5arl*h`(Q
IMP-00009:导出文件异常结束
a:[9U:r"n3S0成功终止导入，但出现警告。

查看记录SQL的结果：

SQL> CONN TEST/TEST
[(yG7|Y}
CFo0已连接。ITPUB个人空间-VoB,@B3U,V@
SQL> SET LONG 100000
Fi^(u7hY5g&n~!O0SQL> SELECT SQL_STATMENT FROM T_LOG;

SQL_STATMENT
3w*rZEW D2]0F0--------------------------------------------------------------------------------
g
X2eB2U/L0CREATE TABLE "SHGOV_ORDER" ("ORDER_ITEM_ID" CHAR(24) NOT NULL ENABLE, "PLAT_ID"
M7}&P$iUX0CHAR(24), "PRODUCT_ID" CHAR(24), "CODE" VARCHAR2(50), "C_NAME_CHN" VARCHAR2(300)ITPUB个人空间.C*v xk/T9J*j y:e
.ITPUB个人空间f6_Z Cj G6Z
.
5pq x)ZC"{{G0.ITPUB个人空间1U+On#R4l-dp Pn
ANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPA
y#s,qm6^0CE "SH_GOV" LOGGING NOCOMPRESS

CREATE TABLE "SHGOV_ORDER" ("ORDER_ITEM_ID" CHAR(24) NOT NULL ENABLE, "PLAT_ID"
n`o!f/jS(N4Q0CHAR(24), "PRODUCT_ID" CHAR(24), "CODE" VARCHAR2(50), "C_NAME_CHN" VARCHAR2(300)
2_"@/}7[|C(U"D0.
sb+@!V$}S eq+k0.
(z5D&l4n2qZ$h"M0.
M}[ x B0ATE, "SENDE_DATE" DATE, "PRICE_RATE" NUMBER(12, 3))  PCTFREE 10 PCTUSED 40 INITRITPUB个人空间!O1M)~&]_-h
ANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
)}T]d2|4~,[0            LOGGING NOCOMPRESS

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "SHGOV_ORDER" ("ORDER_ITEM_ID", "PLAT_IDITPUB个人空间sKE*]{
", "PRODUCT_ID", "CODE", "C_NAME_CHN", "MEDICAL_ID", "MEDICAL_CODE", "TRADE_NAME
8V@'J `Z b0", "USE_UNIT", "USED_NAME", "MANUFACTURE_ID", "MANUFACTURE_NAME", "MANUFACTURE_AITPUB个人空间1l'MZ!i]O]E0y
BBR", "M_SPELL_ABBR", "STAND_RATE", "SPEC", "E_NAME_CHN", "JX_NAME_CHN", "WRAP_NITPUB个人空间%q$S:o2CU |5y%s2`X
AME", "TAX_PRICE", "NATIONAL_RETAIL_PRICE", "TRADE_RATE", "MED_INSURE", "ORDER_IITPUB个人空间 WI5S#wm6R`!KwBb+N
D", "ORDER_AMOUONT", "SEND_AMOUNT", "RECEIVE_AMOUNT", "UNIT_PRICE", "SOURCE_TYPE
i ^1D_.m0", "MAX_PRICE", "HIS_NAME", "HIS_ABBR", "HIS_ID", "DEALER_NAME", "DEALER_ID", "D
Fo1kqb0EALER_ABBR", "MIN_PRICE", "SENDER_NAME", "SENDER_ID", "SENDER_ABBR", "CREATE_DATITPUB个人空间C7F)C#FB'v-n_C
E", "SENDE_DATE", "PRICE_RATE") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,ITPUB个人空间w,r^rud\]Z
 :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,ITPUB个人空间wq4U-o{0YZ-k
 :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42,ITPUB个人空间sr Z|ss
 :43)

看来即使是让触发器报错，捕获到的最终出错SQL也是导入的SQL语句，看来试图利用触发器捕获异常数据的方法，基本上是行不通的。