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时,该拼接结果直接显示为,可在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();
}
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简单易用。
三、补充
listagg(列名,'分隔符')除了要多嵌套一层子查询,其实也挺方便的,它是从11g起才出现的聚合函数,要实现去重和统计,需要使用distinct的多列去重。
select A.id, t.countname, t.pjname
from A
left join (SELECT id,count(name) as countname,
LISTAGG(to_char(name), ',') WITHIN GROUP(ORDER BY name) AS pjname
FROM (select distinct B.id as id, B.name as name
from B
left join C
on B.name = C.name
where C.gender = 'female')
where 1 = 1
group by id) t
on A.id = t.id
where countname is null or countname <= 1;
四、参考文章