相关文章推荐
不要命的芒果  ·  android ...·  23 小时前    · 
豪爽的花生  ·  AttributeError: ...·  1 月前    · 
瘦瘦的青蛙  ·  在使用Dask ...·  10 月前    · 
重感情的机器人  ·  concat 函数 - Azure ...·  11 月前    · 
本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议 》和 《 阿里云开发者社区知识产权保护指引 》。如果您发现本社区中有涉嫌抄袭的内容,填写 侵权投诉表单 进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。 wm_concat、Listagg、XMLAGG ora06502-character string buffer to small varchar2类型最多支持4000字符(如果是中文会更短到2000) 那无论是wm_concat函数还是listagg函数,在处理超过4000字符(如果是中文会更短到2000)长度的时候都会报错,提供(xmlagg(xmlparse(content 合并字段||',' wellformed) order by 排序字段).getclobval() )给大家使用来解决此问题!
+关注继续查看

前言

有朋友开发过程中用到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> 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函数替代。

MySql常用函数(逻辑判断,字符串处理,日期函数)FIND_IN_SET、IF、ISNULL、IFNULL、NULLIF、SUBSTR、SUBSTRING_INDEX、CONCAT、LENGTH
MySql常用函数(逻辑判断,字符串处理,日期函数)FIND_IN_SET、IF、ISNULL、IFNULL、NULLIF、SUBSTR、SUBSTRING_INDEX、CONCAT、LENGTH
鱼找水需要时间