Oracle的WM_CONCAT的局限性

背景

业务上会使用WM_CONCAT函数拼接相关字段，某一天生产上突然报错：ORA-06502错误，即字符串缓冲区太小。排查代码，发现是一张视图的相关字段中使用了wm_concat函数，由于业务量的问题，导致今天出现了问题。

处理

使用xmlagg函数代替wm_concat函数，即可解决这个报错。

XMLAGG函数语法

xmlagg函数是将输入的值转换为xml，处理返回结果也是xml，最后再用getclobval()获取colb类型的结果，其语法如下：

xmlagg(xmlelement(表名, 输入值1，输入值2) order by 参数).extract('//text()').getclobval() 

举例：

SELECT a.scheme_code, a.value_str, XMLAGG(XMLPARSE(CONTENT to_char(sd.label) || ',' WELLFORMED) ORDER BY a.scheme_code, a.value_str).GETCLOBVAL() FLAG
FROM PRD_BSC_SCH a
left join sys_dict sd
on   sd.TYPE = 'PRJ0003'
and  ',' || A.VALUE_STR || ',' LIKE '%,' || sd.value || ',%'
where a.key_name_en = 'YWLY'
group by a.scheme_code, a.value_str

写在后面的话

1. wm_concat函数在Oracle11G和12G版本中已失效；
2. xmlagg函数不会过滤null；
3. 除了wm_concat函数和xmlagg函数外，Oracle中实现同样功能的聚合函数还有个listagg（同样会产生字符串过长的报错）；
4. 相关语法：

-- wm_concat函数的语法：
wm_concat(字段名)
--举例：
select t.nation,wm_concat(t.city) as citys from a_test group by t.nation;

--listagg函数语法：
listagg(字段名,分隔符) within group(order by 字段名)
--举例
select t.nation,listagg(t.city,',') within group(order by t.city) as citys 
from a_test t 
group by t.nation;