Listagg function and ORA-01489: result of string c

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

Answer 2

Why not use nested tables?

set echo on;
set display on;
set linesize 200;

drop table testA;
create table testA
(
col1 number,
col2 varchar2(50)
);

drop table testB;
create table testB
(
col1 number,
col2 varchar2(50)
);

create or replace type t_vchar_tab as table of varchar2(50);

insert into testA values (1,'A');
insert into testA values (2,'B');

insert into testB values (1,'X');
insert into testB values (1,'Y');
insert into testB values (1,'Z');
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

-- test size > 4000
insert into testB
select 2 as col1, substr((object_name || object_type), 1, 50) as col2
from all_objects;
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

I'm no java expert, but this has been around for some time and I'm sure java can pull the values out of the nested table. And, no need to tokenize some delimited string on the other end.

Answer 3

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.

No they don't. Scroll down and you'll see several options that don't require pl/sql.