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层直接运行仍报错。

三、问题解决

1. 去掉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

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中对多行查询结果进行拼接 - 天涯已可