问题描述:
使用java语言搭建的web程序,使用了spring data jpa框架。由于页面显示的需求,我采用了视图的方式进行多表查询,方便快捷。开发环境用的oracle是10G,测试环境是11G,所以导致了“wm_concat 函数导致ORA-22922: 不存在的 LOB 值”问题出现。
原因分析:
原来,wm_concat函数在10G返回的是字符串,到了11G返回的是LOB类型,在java程序里面是使用字符串类型,导致了问题的出现:
假设有视图A,A视图有id和name字段
10G版的SQL:
select wmsys.wm_concat(distinct(a.name) from A a 没问题
网上大部分的答案的SQL:
select to_char(wmsys.wm_concat(distinct(a.name)) from A a 有问题
如果这个视图的字段都有值,不会报错。当name为空是,java程序一样会报“ORA-22922: 不存在的 LOB 值”
解决方案:
这时候应该选择使用listagg函数去替换wmsys.wm_concat函数(行转列)
select listagg(a.name,‘,’ ) within GROUP (order by (a.name)) from A a
这样,为空的字段则会是字符串类型了。
注意:如果报错:ORA-01489: 字符串连接的结果过长
当连接的字符串超过Oracle字符串最大长度限制时,您可以使用XMLAGG
函数来解决这个问题。XMLAGG
函数将连接的字符串作为XML元素进行聚合,并且没有长度限制。您可以按照以下方式修改您的查询语句:
SELECT
ORDER_ID,
RTRIM(
XMLAGG(XMLELEMENT(E, goods_name || ',数量:' || ORDER_NUM || '; ') ORDER BY ORDER_ID).EXTRACT('//text()').GETCLOBVAL(),
'; '
) AS goods_name
FROM
B2B_ORDER_ITEM
WHERE
dr = 0
-- AND ORDER_ID = '0ganVBGYcr4Cxxs0o77o'
GROUP BY
ORDER_ID;
在上述示例中,我使用XMLAGG
函数将连接的字符串作为XML元素进行聚合。然后,使用EXTRACT
函数提取聚合后的字符串,并使用GETCLOBVAL()
将其转换为CLOB类型。最后,使用RTRIM
函数去除末尾的分号和空格。