前言
有朋友开发过程中用到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