java - Listagg function and ORA-01489: result of string concatenation is too long

Question

When i run the following query:

 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 returns the following error:

 ORA-01489: result of string concatenation is too long

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.

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.

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).

Answer 1

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;

eg: http://sqlfiddle.com/#!4/083a2/1