当执行sql:
select wm_concat(colA) as colA from tableA
提示:
java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在 "WMSYS.WM_CONCAT_IMPL", line 30 )
wm_concat的最大长度只有4000,超过就会报错,
两种方法:
(1)转clob类型
select --wm_concat(colA) as colA
rtrim(xmlagg(xmlparse(content colA || ',' wellformed) ORDER BY colA).getclobval(),',') as colA from tableA;
(2)先转clob,再转varchar2,但是长度还是只能4000
select --wm_concat(colA) as colA
dbms_lob.substr(rtrim(xmlagg(xmlparse(content colA || ',' wellformed) ORDER BY colA).getclobval(),','),4000) as colA from tableA;
这是别人的方法:http://blog.csdn.net/l2tp1012/article/details/30744371