现在有这么一个需求，业务单据上面有很多流程跟踪的信息，要显示在列表页面的一个字段中。可能出现一个问题，那就是如果合并的字段长度超过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;