Skip to Main Content

Listagg function excdding 4000 characters

SamFisherJun 6 2013 — edited Jun 7 2013
Hello All,
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

I'm using listagg function to concatenate the column values. I have nearly 4000 records in the table. I guess listagg function can concatenate till 4000 characters and if it exceeds 4000 then I'm getting an Ora error which says 'results of string concatenation is too long'. 

case 1:
select listagg(id, ',') within group (order by id) from (select level as id from dual connect by level < 1000);
No Issue.

Case 2:
select listagg(id, ',') within group (order by id) from (select level as id from dual connect by level < 1050);
Ora-01489

How to handle the issue? 
Thanks,
Shank.
Comments
Post Details
Added on Jun 6 2013
5 comments
127,516 views