oracle 11g行字段拼接wmsys.wm_concat问题not a lob
一、问题出现
项目中的某个查询需要将表中某个字段不重复地拼接起来,百度得到该函数wmsys.wm_concat(字段)
,以及listagg(字段,连接符)
函数,前者只能使用逗号’,
‘连接,后者可以定制连接符。
但由于listagg不能直接在参数中使用distinct
去重,因此采用wm_concat
函数。
sql格式如下:
select t.id,t.pjname from(select a.id as id,count(distinct b.name) as countname,to_char(wmsys.wm_concat(distinct to_char(b.name))) as pjname from a left join b on a.id = b.id where 1 = 1 group by a.id) t where t.countname > 1
这段sql的作用是,以a表的id为组,不重复的拼接b表的name,并统计name去重后的个数,最后返回name去重后仍多于1个的id和拼接name。
开发时这段sql是正常的,然而,这段sql在测试库上却会报错ora-22922: 不存在的 lob 值
。
二、原因分析
经网上查资料,发现问题出在wmsys.wm_concat
函数在oracle不同版本中的返回值类型不同。
该项目开发使用的是oracle 11.2.0.1.0,而测试与现场使用的均为oracle 11.2.0.4.0,项目开始时的疏忽导致开发与测试的不一致。
将拼接函数外的to_char去掉后,sql不会报错,但对象不是string类型(可能是java.sql.clob
类型),无法直接tostring获得。
同时,在plsql developer 9.0中直接运行sql时,该拼接结果直接显示为<clob>
,可在select结果中使用to_char()
函数,而该函数在项目dal层直接运行仍报错。
三、问题解决
- 去掉wm_concat函数外的to_char()
select t.id,t.pjname from(select a.id as id,count(distinct b.name) as countname,wmsys.wm_concat(distinct to_char(b.name)) as pjname from a left join b on a.id = b.id where 1 = 1 group by a.id) t where t.countname > 1
- 将lob类型对象转换为string类型,有两种方法:在sql中使用oracle函数,或者在后端dal层转换,参考网上的文章,我选择后者,因为完整的sql要实现的功能本身比较复杂,要尽量简化在数据库中的操作。
- 获取结果集中的字段并判断
string array1 = ""; try { array = array[1].getclass().tostring().equals("class java.lang.string") ? array[1].tostring() : clobtostring((clob) array[1]); } catch (sqlexception e) { array14 = array[1].tostring(); } catch (ioexception e) { e.printstacktrace(); }
- 转换clob为string对象(参考oracle中将clob字段转换成字符串)
public string clobtostring(clob clob) throws sqlexception, ioexception { string restring = ""; reader is = clob.getcharacterstream(); bufferedreader br = new bufferedreader(is); string s = br.readline(); stringbuffer sb = new stringbuffer(); while (s != null) { sb.append(s); s = br.readline(); } restring = sb.tostring(); if(br!=null){ br.close(); } if(is!=null){ is.close(); } return restring; }
问题解决。还是得看看listagg方法的用法,毕竟官方兼容性强些,但觉得listagg
不如wm_concat
简单易用。
三、参考文章
- oracle中将clob字段转换成字符串 – csdn博客
- ora-22922: 不存在的 lob 值解决办法 – csdn博客
- oracle中对多行查询结果进行拼接 – 天涯已可