Lutz Haake | Freelance Oracle Consultant

Aggre­gating a large number of objects into one string can lead to ORA-01489, due to the 4000 character limit of the listagg function.

As an example, let’s try to conca­tenate all user names in the database (referred to as “object”).

select
 listagg(object, ',') within group (order by user_id) objects
 from
 (
 select user_id, username object
 from
 dba_users
 )
 ;

ORA-01489: Das Ergebnis der Zeichen­fol­gen­ver­kettung ist zu lang
01489. 00000 — “result of string conca­te­nation is too long”
*Cause: String conca­te­nation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

The workaround is to use the xmlagg function.

select
 rtrim(xmlagg(xmlelement(e,object,',').extract('//text()') order by user_id).GetClobVal(),',') objects
 from
 (
 select user_id, username object
 from
 dba_users
)
;

The structure of the sql stays the same.