Listagg函数和ORA-01489：字符串连接的结果太长 [英] Listagg function and ORA-01489: result of string concatenation is too long

问题描述

当我运行以下查询时：

 Select
  tm.product_id,
  listagg(tm.book_id || '(' || tm.score || ')',',')
    within group (order by tm.product_id) as matches
from
  tl_product_match tm 
where
  tm.book_id is not null 
group by
  tm.product_id

Oracle返回以下错误：

Oracle returns the following error:

 ORA-01489: result of string concatenation is too long

我知道失败的原因是listagg函数试图连接大于4000的值不支持的字符。

I know that the reason it is failing is that the listagg function is trying to concatenate a the values which are greater than 4000 characters which is not supported.

我已经看到了这里描述的另一个例子 - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php 但它们都需要使用功能或程序。

I have seen the alternative example described here - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php but they all require the use of functions or procedure.

是否存在纯SQL的解决方案，而无需调用函数或存储过程并且能够使用标准JDBC读取值？

Is there a solution that is pure SQL without having to call a function or stored procedure and being able to read the value using standard JDBC?

我遇到的另一个困难是我见过的大多数字符串聚合示例都显示了如何按原样读取值的示例。在我的例子中，我首先修改了值（即我正在聚合两列）。

The other difficulty i have is that most string aggregation examples i have seen shows examples with how to read the value as is. In my example about i am modifying the value first (i.e. i am aggregating two columns).

推荐答案

您可以使用xml函数来执行返回CLOB的操作。 JDBC应该没问题。

you can use xml functions to do it which return a CLOB. JDBC should be just fine with that.

select tm.product_id, 
       rtrim(extract(xmlagg(xmlelement(e, tm.book_id || '(' || tm.score || '),')), 
               '/E/text()').getclobval(), ',')
  from tl_product_match tm
 where tm.book_id is not null 
 group by tm.product_id;

例如： http://sqlfiddle.com/#!4/083a2/1

这篇关于Listagg函数和ORA-01489：字符串连接的结果太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！