分隔CLOB字段报错

Question

分隔CLOB字段报错,

今天写了一个函数 用来分隔clob字段中的数据.

新建了以下类型：

1，对象类型 o_gmpcreate or replace type o_gmp as object

(

  groupid     number,

  cpids           clob,

  cchids    clob

)2,对象数组类型 to_gmp:create or replace type to_gmp is tableof o_gmp

3,新建函数 f_split_clob:create or replace functionf_split_clob(vr_cur   to_gmp,

                                      vi_split in varchar2 default ',')

  return to_gmp is

  vi_pos11 int := 1;

  vi_pos12 int := 1;

  vi_pos21 int := 1;

  vi_pos22 int := 1;

  vt_parm  to_gmp := to_gmp(o_gmp('', empty_clob(),empty_clob()));

  vr_curpm o_gmp;

  i        int := 0;

  vv_p     varchar(40);

begin

  for x in 1 .. vr_cur.count loop

    vr_curpm := o_gmp(vr_cur(x).groupid,

                     vr_cur(x).cpids      ,

                     vr_cur(x).cchids    );

    vi_pos11 := 1;

    vi_pos12 := 1;

    vi_pos21 := 1;

    vi_pos22 := 1;

    loop

      vi_pos12 := instr(vr_curpm.cpids      , vi_split, vi_pos11);

      vi_pos22 := instr(vr_curpm.cchids    , vi_split, vi_pos21);

   

      exit when vi_pos12 = 0 or vi_pos22 = 0;

      i := i + 1;

      if not vt_parm.exists(i) then

        vt_parm.extend;

        vt_parm(i) := o_gmp('', empty_clob(),empty_clob());

      end if;

      vt_parm(i).groupid := vr_curpm.groupid;

      if vi_pos12 > 0 then

        vt_parm(i).cpids       :=substr(vr_curpm.cpids      ,

                                  vi_pos11,

                                  vi_pos12 - vi_pos11);

      end if;

      if vi_pos22 > 0 then

        vt_parm(i).cchids    := substr(vr_curpm.cchids    ,

                                       vi_pos21,

                                       vi_pos22 - vi_pos21);

      end if;

      vi_pos11 := vi_pos12 + 1;

      vi_pos21 := vi_pos22 + 1;

    end loop;

    vv_p := trim(substr(vr_curpm.cpids      , vi_pos11, length(vr_curpm.cpids      )));

  

    if trim(vv_p) <> '' and trim(vv_p) is not null then

      vt_parm.extend;

      vt_parm(i) := o_gmp('', empty_clob(), empty_clob());

      vt_parm(i).groupid := vr_curpm.groupid;

      vt_parm(i).cpids      := substr(vr_curpm.cpids      ,

                                vi_pos11,

                                length(vr_curpm.cpids      ));

   

      vt_parm(i).cchids   := nvl(substr(vr_curpm.cchids    ,

                                         vi_pos21,

                                         vi_pos22 - vi_pos21),

                                  '0');

   

    end if;

  

  end loop;

  return vt_parm;

end;4，测试函数：

原始表中的数据表名： tmp_mpgroupclob

表结构： SQL> desc tmp_mpgroupclob

Name        Type        Nullable Default Comments

----------- ------------ -------- ------- --------

GROUPID     VARCHAR2(10)                        

CPIDS           CLOB         Y                       

CCHIDS    CLOB        Y                        表数据示例：SQL> select * from tmp_mpgroupclobwhere rownum<=3;

GROUPID    CPIDS                                                                            CCHIDS   

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1352      122231000100001152|122231000100001153|122231000100001152|1222310001000011521|    1|2|3|4|

目标表是 ：tmp_mpgrouplist

结构是：SQL> desc tmp_mpgrouplist

Name        Type        Nullable Default Comments

----------- ------------ -------- ------- --------

GROUPID     VARCHAR2(10) Y                        

CPIDS           VARCHAR2(40) Y                        

CCHIDS    VARCHAR2(10) Y  

用以下代码把 tmp_mpgroupclob表中的数据分隔出来 插入到tmp_mpgrouplist中，安装 ‘|’ 号分隔开来。示例：

1352122231000100001152|122231000100001153|122231000100001152|1222310001000011521|    1|2|3|4|

变成

1352 122231000100001152   1

1352 122231000100001153  2

1352 122231000100001152  3

1352 122231000100001152  4

测试代码：declare

  vto_gmp  to_gmp;

  vi_count int;

  cursor cur1 is

    select o_gmp(groupid, cpids     , cchids    )

      from tmp_mpgroupclob

     where rownum <= 420;

begin

  open cur1;

  loop

    fetch cur1 bulk collect

      into vto_gmp limit 10;

    exit when cur1%notfound;

    insert into tmp_mpgrouplist

      (groupid, cpids     , cchids    )

      select groupid, cpids      , cchids   

      

        from table(f_split_clob(vto_gmp, '|'));

    commit;

  end loop;

  close cur1;

end;

注意红色部分的条件。因为表 tmp_mpgroupclob中有记录几千条，当处理记录少的时候上述代码没有问题，但是当处理记录多了话比例上面处理420条，就会报错：

已经分批处理了。 fetch cur1 bulk collect

      into vto_gmp limit 10;怎么还报错呢？ ORA-04030: 在尝试分配 16396 字节 (koh-kghu call ,kollalos2) 时进程内存不足,

如果只处理 tmp_mpgroupclob 表中的少一些数据就不会报上面这个错误，这个错误时什么意思。是执行f_split_clob的错误，还是table函数的错误呢？

Answer 1

我上面写那个主要开销就是在处理临时表的时候，所以你可以将临时表换成内存表就可以了，我又修改了下：

，看起来不那么复杂吧：

1. CREATE OR REPLACE FUNCTION split2var(inclob IN CLOB) RETURN VARCHAR2 AS
2.   v_clob VARCHAR2(200) := inclob;
3.   v_char VARCHAR2(300) := ' ';
4.   v_cnt  INT := 1;
6.   TYPE c_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
7.   v_c c_type;
9. BEGIN
11.   WHILE v_char IS NOT NULL
12.   LOOP
13.     v_char := regexp_substr(v_clob, '[^|]{1,}', 1, v_cnt);
14.     v_char := TRIM(v_char);
15.   
16.     v_c(v_cnt) := v_char;
17.     v_cnt := v_cnt + 1;
18.   
19.   END LOOP;
21.   v_cnt := (v_cnt - 1) / 2;
23.   v_char := v_c(1) || '..' || v_c(v_cnt) || chr(10) || v_c(2) || '..' ||
24.             v_c(v_cnt + 1) || chr(10) || v_c(3) || '..' || v_c(v_cnt + 2) ||
25.             chr(10) || v_c(4) || '..' || v_c(v_cnt + 3);
27.   RETURN v_char;
29. END;
30. /
32. SELECT COUNT(split2var(x)) cnt FROM clob_tab;

复制代码

Answer 2

今天发现正则表达式虽然好用，但是效率太低了。

regexp_substr

就上面的这个例子我用 regexp_substr处理一个保护有2700个|符合分隔的clob字段需要几分钟，而用substr截取就是我的老方法只要几秒钟。相差太大了。

Answer 3

发现一个问题，如果用limit获取一个游标的数据。

当记录结果没有达到limit要求的数据时数据取不到。

fetch vrf_cur bulk collect

      into vto_gmp limit 10;

比如这个，目的是取循环取10条，分批处理。但是当最后一轮还剩下没有10的话，这些记录就取不到了。相当于丢了。

Answer 4

这个我还没有用过。我试试

Answer 5

主要是你的这个串有规律，所以用了lead和ntile来取

Answer 6

regexp_substr(v_clob, '[^|]{1,}', 1, v_cnt);这个用得好啊。方法很简便

Answer 7

我也写了一个，练练手

1. CREATE GLOBAL TEMPORARY TABLE var_temp(ID INT,val VARCHAR2(4000))
2. ON COMMIT PRESERVE ROWS;
3. /
5. DECLARE
6.   v_clob CLOB := '1352122231000100001152|122231000100001153|122231000100001152|1222310001000011521|    1|2|3|4|';
7.   v_len  INT;
8.   v_char VARCHAR2(300) := ' ';
9.   v_cnt  INT := 1;
11.   TYPE t_tab IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;
12.   v_tab t_tab;
13. BEGIN
14.   
15.   EXECUTE IMMEDIATE 'truncate TABLE var_temp';
16.   
17.   v_len := LENGTH(v_clob);
18.   
19.   WHILE v_char IS NOT NULL
20.   LOOP
21.     v_char := regexp_substr(v_clob, '[^|]{1,}', 1, v_cnt);
22.     v_char := TRIM(v_char);
23.     v_cnt  := v_cnt + 1;
24.    
25.     IF v_char IS NOT NULL
26.     THEN
27.       INSERT INTO var_temp VALUES (v_cnt - 1, v_char);
28.     END IF;
29.    
30.   END LOOP;
31.   
32.   COMMIT;
34.   SELECT COUNT(*) / 2 INTO v_cnt FROM var_temp;
36.   EXECUTE IMMEDIATE '
37.   SELECT col
38.   FROM (SELECT val || ''  '' || LEAD(val,' || v_cnt ||
39.                     ') OVER(ORDER BY ID) AS col,
40.                ID,
41.                NTILE(2) OVER(ORDER BY ID) AS grp
42.           FROM var_temp)
43. WHERE grp = 1
44. ORDER BY ID' BULK COLLECT
45.     INTO v_tab;
47.   FOR v_idx IN 1 .. v_tab.count
48.   LOOP
49.     dbms_output.put_line(v_tab(v_idx));
50.   END LOOP;
52. END;
53. /
55. 1352122231000100001152  1
56. 122231000100001153  2
57. 122231000100001152  3
58. 1222310001000011521  4

复制代码

Answer 8

哎呀，自己找到问题了。

Answer 9

这个测试有1个目的：

测试使用table函数