用forall的save exceptions机制高效处理数据交换中的异常数据

在一个数据交换场景中，对方提供一个远程数据库，我方根据时间戳提取增量数据，经转换处理后存到我方数据库的表中。

对方数据有以下特征：

1.增量数据数量较大；
2.数据不规范，部分数据无法直接写入我方数据库。

考虑有以下几个方法来处理：

方案一、对每条数据loop循环处理，写入错误则记录后跳过；
方案二、在insert时对数据进行函数校验，符合条件的才写入；

仔细考虑这2中方案：
方案一对每条记录做loop循环无疑耗费了大量时间在远程链路上，对于数据量较大的情况时间代价太大。
方案二如果校验比较复杂，则用于函数校验的成本也很大；再者对错误数据的处理可能会造成更多的工作量。

详细分析后，我们提出使用forall的save exception机制来处理这个场景。
假设对方数据库远程链接名是db2，远程表t_exch结构如下：

SQL> desc t_exch
Name       Type           Nullable Default Comments 
---------- -------------- -------- ------- -------- 
GUID       VARCHAR2(50)                             
NAME       VARCHAR2(30)   Y                         
STIME      VARCHAR2(20)   Y                         
ETIME      VARCHAR2(20)   Y                         
CONTENT    VARCHAR2(4000) Y                         
STATUS     VARCHAR2(1)    Y                         
CREATEDATE DATE           Y                         
MOBILE     VARCHAR2(20)   Y                         

SQL> select * from t_exch where rownum<5; 
GUID                                               NAME                           STIME                ETIME                CONTENT                                                                          STATUS CREATEDATE  MOBILE
-------------------------------------------------- ------------------------------ -------------------- -------------------- -------------------------------------------------------------------------------- ------ ----------- --------------------
9C67F1AFC648469FAD071E8C52413AE3                   HELP                           2013-10-09 18:32:02  20131009183203       SYSTEMHELP14237TABLE                                                             0      2013/10/9 1 0138423714237
DDEB9DA235FB4077BA7382A9600F50B2                   HS_BULKLOAD_VIEW_OBJ           2013-10-09 18:30:07  20131009183007       SYSHS_BULKLOAD_VIEW_OBJ13212TABLE                                                0      2013/10/9 1 0138321213212
3D7C5ADC00BA4C74AA9FC3629A5FE999                   DR$THS                         2013-10-09           201310091            CTXSYSDR$THS68402TABLE                                                           0      2013/10/9 1 16840268402
68FA40380F79456488B279ACA5FE1E84                   HS_PARTITION_COL_NAME          2013-10-09 18:30:07  20131009183007       SYSHS_PARTITION_COL_NAME13219TABLE                                               0      2013/10/9 1 0138321913219

我方数据库表结构如下：

create table T_RECV
(
  guid       VARCHAR2(50) not null,
  name       VARCHAR2(30),
  stime      DATE,
  etime      DATE,
  content    VARCHAR2(50),
  status     VARCHAR2(1),
  createdate DATE,
  mobile     VARCHAR2(11)
);

两边表结构的区别有：

1.时间字段从源数据的Varchar2型变成date型，
2.content字段长度缩小到50位
3.mobile字段缩小到11位

现在用forall来进行处理：

1、建错误记录表

create table T_EXCH_ERR
(
  guid       VARCHAR2(50) not null,
  name       VARCHAR2(30),
  stime      VARCHAR2(20),
  etime      VARCHAR2(20),
  content    VARCHAR2(4000),
  status     VARCHAR2(1),
  createdate DATE,
  mobile     VARCHAR2(20),
  ERRMSG     VARCHAR2(4000)
);

2.批量处理的存储过程

create or replace procedure P_EXCH(DT_IN DATE) is

  TYPE TAB_EXCH IS TABLE OF T_EXCH_ERR%ROWTYPE;
  L_TAB_EXCH TAB_EXCH;
  ERR  EXCEPTION;
  PRAGMA EXCEPTION_INIT(ERR, -24381 );
  ERRMSG VARCHAR2(4000);
  bad_id PLS_INTEGER;
begin
  --将增量数据批量提取到本地集合变量
  SELECT GUID,NAME,STIME,ETIME,CONTENT,STATUS,CREATEDATE,MOBILE,NULL
  BULK COLLECT INTO L_TAB_EXCH
  FROM T_EXCH@DB2
  WHERE CREATEDATE>=DT_IN;
  --执行forall批量写入
  FORALL I IN L_TAB_EXCH.FIRST .. L_TAB_EXCH.LAST SAVE EXCEPTIONS
    INSERT INTO T_RECV
      (GUID,NAME,STIME,ETIME,CONTENT,STATUS,CREATEDATE,MOBILE)
      VALUES(L_TAB_EXCH(I).GUID,L_TAB_EXCH(I).NAME,TO_DATE(L_TAB_EXCH(I).STIME,'YYYY-MM-DD HH24:MI:SS')
            ,TO_DATE(L_TAB_EXCH(I).ETIME,'YYYYMMDDHH24MISS'),L_TAB_EXCH(I).CONTENT
            ,L_TAB_EXCH(I).STATUS,L_TAB_EXCH(I).CREATEDATE,LTRIM(L_TAB_EXCH(I).MOBILE,'0'));
  COMMIT;
EXCEPTION WHEN ERR THEN
--处理24381错误
  FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
    ERRMSG := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    bad_id:= SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
    --记录错误数据
    L_TAB_EXCH(BAD_ID).ERRMSG:=ERRMSG;
    INSERT INTO T_EXCH_ERR VALUES L_TAB_EXCH(bad_id);
  END LOOP;
  COMMIT; 
WHEN OTHERS THEN
  ERRMSG:=SQLERRM;
  DBMS_OUTPUT.PUT_LINE(ERRMSG);
end P_EXCH;

3.执行结果

SQL> EXEC P_EXCH(DATE'1900-01-01');
PL/SQL procedure successfully completed

SQL> select count(*) from T_EXCH@db2;
  COUNT(*)
----------
        76

SQL> select count(*) from T_RECV;
  COUNT(*)
----------
        70

SQL> select GUID,ERRMSG from T_EXCH_ERR;
GUID                                               ERRMSG
-------------------------------------------------- --------------------------------------------------------------------------------
9C67F1AFC648469FAD071E8C52413AE3                   ORA-12899: value too large for column  (actual: , maximum: )
DDEB9DA235FB4077BA7382A9600F50B2                   ORA-12899: value too large for column  (actual: , maximum: )
1D49CD5E15D646DFBADBE5DF158BF05B                   ORA-12899: value too large for column  (actual: , maximum: )
68FA40380F79456488B279ACA5FE1E84                   ORA-12899: value too large for column  (actual: , maximum: )
125CCAA9EDCE4EF4A3621DAE9E148324                   ORA-12899: value too large for column  (actual: , maximum: )
3D7C5ADC00BA4C74AA9FC3629A5FE999                   ORA-01840: input value not long enough for date format
6 rows selected

可以看到正确的数据全部写入了我方数据表中，而有问题的记录写到了错误记录表，并且记录了ORA错误信息。
将错误的信息反馈给对方进行数据修复即完成了数据全量交换。