通过wm_concat 函数报错：ora06502-character string buffer to small浅谈wm_concat、Listagg、xmlagg函数的使用和结果不确定性

---

前言

有朋友开发过程中用到wm_concat 函数拼接两个字段，写完sql运行时报错：ora06502-character string buffer to small
这个报错是因为此拼接函数返回值是varchar2类型，而varchar2类型最多支持4000字符(如果是中文会更短到2000)，那么遇到这个问题怎么处理呢？
另外就是可能很多人已使用过wmsys.wm_concat函数，但wmsys.wm_concat是一个非公开函数，具有不确定性（返回值原来是varchar,Oracle11.2下就成了clob)。从Oracle11.2开始就有了分析函数listagg来替代它，到了Oracle 12C+及以后，wmsys.wm_concat函数已经被遗弃了，所以建议各位不要再使用这个函数了！而是使用listagg函数替代。
至于listagg函数对于拼接超过4000长度的字符也会报错，因为它的返回值也是varchar类型。
综上，那无论是wm_concat函数还是listagg函数，在处理超过4000字符(如果是中文会更短到2000)长度的时候都会报错，接下来博主提供另外一个现成的函数(xmlagg(xmlparse(content 合并字段||‘,’ wellformed) order by 排序字段).getclobval() )给大家使用来解决此问题！！！

---

一、ora06502-character string buffer to small问题复现

先生成一个1001行的表，每列4个英文字符，相当于如果拼接成一个字符串，应该是4004个字符长度。

create table tt as 
select 'zhao' as ename from dual connect by level <=1001;

先用listagg做一下测试：

SQL> select listagg(ename)within group(order by 1) from tt;
select listagg(ename)within group(order by 1) from tt

ORA-01489: result of string concatenation is too long

SQL> 

发现会因为字符过长报错，虽然报的错不是wm_concat的错误，但是这个问题与标题的错误是同类。
还记得我前言说的不？
wmsys.wm_concat是一个非公开函数，具有不确定性（返回值原来是varchar,Oracle11.2下就成了clob)，接下来我找个11.2的库给大家验证一下！！！
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

SQL> drop table aa purge;

Table dropped


SQL> 
SQL> create table aa as
  2  select wm_concat(ename) as aa from tt;

Table created


SQL> desc aa;
Name Type Nullable Default Comments 
---- ---- -------- ------- -------- 
AA   CLOB Y 

SQL> create table bbb as select 'zhaoyd' as dd from dual;

Table created


SQL> desc bbb;
Name Type    Nullable Default Comments 
---- ------- -------- ------- -------- 
DD   CHAR(6) Y                         

SQL> 

看到了没！在11.2中，拼接生成的结果，如果字符超过4000，那么结果就自动变成了clob类型，没有报ora06502-character string buffer to small。而如果像bbb表，结果字符串很短，又变成了char类型。如果你在其他版本做测试，你会发现生成的其实是varchar类型的。所以不建议这个函数的使用，结果具有不确定性，建议大家改成listagg，listagg函数的使用方式如下：

listagg(合并字段,'连接符号') within group (order by 排序字段)

二、解决方案

前面复现了问题，也建议了大家用listagg函数，但是对于超4000长度的字符应该怎么处理呢？
这里给大家介绍xml函数，当使用xmlagg函数拼接超过4000长度的字符时候，不会报错，结果是clob类型，如果长度低于4000的也是clob类型，不过大家可以自己显示的选择生成的数据类型，而不是想wm_concat是的。
下面是我给大家提供的写法案例：

xmlagg(xmlparse(content 合并字段||',' wellformed) order by 排序字段).getclobval() 

xmlagg函数需要将输入的值转换为xml，处理返回结果也是xml，最后再用getclobval()获取colb类型的结果。
在Java中需要用java.sql.Clob类，进行数据的接收与转换。

---

总结

本文主要介绍wm_concat、Listagg、XMLAGG三个字符串拼接函数的使用方式和其中的坑。
很多人已使用过wmsys.wm_concat函数，但wmsys.wm_concat是一个非公开函数，具有不确定性（返回值原来是varchar,Oracle11.2下就成了clob)。从Oracle11.2开始就有了分析函数listagg来替代它，到了Oracle 12C+及以后，wmsys.wm_concat函数已经被遗弃了，所以建议各位不要再使用这个函数了！而是使用listagg、XMLAGG函数替代。