巧用分析函数解决ORA-06502--字符串缓冲区太小

        现在有这么一个需求，业务单据上面有很多流程跟踪的信息，要显示在列表页面的一个字段中。可能出现一个问题，那就是如果合并的字段长度超过4000个字节，SQL语句会报错，ORA-06502  字符串缓冲区太小，即使你用substr()截取也是这样的错误。其实就是一个行转列的过程。那怎么解决这个问题，来做个试验：

drop table test;
create table test(
track_info_id number(10),
id number(10),
DESCR varchar2(1000)
);

insert into test values(4,100,RPAD('4',800,'4'));
insert into test values(5,100,RPAD('5',800,'5'));
insert into test values(6,100,RPAD('6',800,'6'));
insert into test values(2,100,RPAD('2',800,'2'));
insert into test values(3,100,RPAD('3',800,'3'));
insert into test values(1,100,RPAD('1',800,'1'));
commit;

 1. 先判断各行的DESCR加起来是否超过4000个字节，只取没有超过4000个字节的行数。

select ID,wmsys.wm_concat(DESCR)
  from (select ID,
               DESCR,
               sum(lengthb(DESCR||',')) over(partition by ID order by track_info_id asc) descr_length
          from test)
 where descr_length < 4000
 group by ID;

 

2. 这个跟上面的区别在于，只是如果合计超过4000个字节，只取最新的几个流程。

with t as(
  select * from (select ID,DESCR,track_info_id,
  sum(lengthb(DESCR||','))over(partition by ID order by  track_info_id desc) descr_length
          from test ) where descr_length<4000)
select ID,max(DEFECTDES) from
 (SELECT ID,
   wmsys.wm_concat(DESCR) over(partition by ID order by track_info_id) DEFECTDES
   from t) group by ID;