Quote:
Originally Posted by selvam85
Hi All,
Is there a way to retrieve only the first 4000 characters in the result. Like do a substring and restrict the length of the result to 4000 characters so that I get rid of this error.
|
You can calculate a cumulative sum of length, and select only these records
where length<=4000, here isan example:
Code:
select listagg( lev, ',' ) within group (order by lev)
from
( select level lev
from dual
connect by level <= 5000
);
Error report:
SQL Error: ORA-01489: result of string concatenation is too long
select listagg( lev, ',' ) within group (order by lev)
from
(
select lev,
sum( length( lev ) + 1)
over ( order by lev rows between unbounded preceding and current row) lngth
from
( select level lev
from dual
connect by level <= 5000
)
)
where lngth <= 4000
;
LISTAGG(LEV,',')WITHINGROUP(ORDERBYLEV)
-----------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,......
dBforums
>
Issue with Listagg function
02-04-12, 12:29
Linear Mode
