The LISTAGG() function in Oracle is a very useful to aggregate delimited lists of column values into single strings. However, it has a limitation based on the maximum length of VARCHARs within the database. In Oracle 11g, this maximum is 4000 characters. Thankfully, this limitation has been extended to 32,767 in 12c. Here is an example of what you may experience when exceeding this internal limit:
SQL> DESC str_data;
Name Null? Type
----------------------- -------- ----------------
ID NUMBER
VALUE VARCHAR2(30)
SQL> SELECT SUM( LENGTH( value ) ) FROM str_data;
SUM(LENGTH(VALUE))
------------------
4580
SQL> SELECT LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY id DESC )
2 FROM str_data;
SELECT LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY id DESC )
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
In this example, we are simply trying to convert all values into a comma delimited list. However, the total length of the aggregated values exceeds the 11g limit of 4000 characters. This causes an internal overrun when trying to aggregate these values. Let’s say we can loosen the requirements for our return value a little. Maybe we can return as much of the list as possible, but not exceed the system-imposed limit of 4000 characters. Although not very straightforward, additional analytic functions can be used to determine when this maximum is reached and prevent the internal overrun from occurring. In this case, we’ll use a running total in the form SUM( LENGTH( value || ',' ) ) OVER ( ORDER BY id ASC ) to determine at which point we exceed the total character limit. From this row forward, we will begin taking substrings of the values to stay at the limit. The first row to exceed the character limit will be truncated to meet a total of 4000 characters. All strings thereafter will effectively be reduced to NULL. Notice the comma delimiter has been moved out of the LISTAGG() function and into the inner query so we can count the delimiters as part of the 4000 character limit.
SQL> SELECT LISTAGG( value ) WITHIN GROUP ( ORDER BY id ASC ) aggvalue 2 FROM 3 ( 4 SELECT 5 id, 6 CASE 7 WHEN tot_length > 4000 THEN SUBSTR( value, 1, LENGTH( value ) - ( tot_length - 4000 ) ) 8 ELSE value 9 END value 10 FROM 11 ( 12 SELECT id, value || ',' value, SUM( LENGTH( value || ',' ) ) OVER ( ORDER BY id ASC ) tot_length 13 FROM str_data 14 ) 15 ); AGGVALUE -------------------------------------------------- 7WI7POD2EGGHV2T709,AQ6YA2D0S4Z65XN58X,CUX6P8LG2U8B ,D2DV99I0UNJ8C4MPTF62XFAJBM04,OX66C2F765,LXL,2SF11 M4,OMGPOD0LYF60ZLNKBSSEDRA,8PLD95W0BDVUP206LC3LJ8S DL7GEEF,XB12YJWL9S1VPOOQX4V0W02,TX2SNKOXNFYT,ZMGED YB7,JAP3HBEH5R93VL3LJW80OYNA6CIU,3XLOJ1J312,OWITEC K5CMEN9V8PM8XSFB3TA,NYN6E7TQT3,8GJYWHKZ5B,RZCID5MZ 76,2JZD6D7QK4O9L4FDEHE429ZH,B1,1XM5U8T,C4753UKWWEP XVKR2H593U1,7BUVECHZY9MIHCZ0ZO,EWVS9I2W8P9DYOY3KFC KU,18E7JR,5UO,QTUQDQ43LVYM8Q3WOKV5PXT4FKBOXK,F28P1 P8N,8,HERBBW3M7VRH45E5,BADJO3NOXYQXW,CKH8,0I7JK,1C LHD4DZL7OC66BQ29S6Z,D0M4OLOZZCP55HS1WJYM8AI,7BDTO7 BXPNHA224DCBCUTY7890,1GINLBZXDS0XP06F7KWE9UAG28,3H B9V5PYI1NENU212DIS1E,MH4BB4P9Q2N9TIIYFQOL22NXHM,2H H4CLMML0U,PLOX86C1HT14U31S33HETLTMBPAUT,HN1ELU9EOF AN,XLIXGO41FLOL1BY,1KTPI7NY4IQ7DRYBF4Q20TB,F22PCDD VT,EXIAE148,TG2CU,FN2491GMWEVL0TXR45V2DD,DVWHWO45I QRHNEBG4,T3B8ZVDAPIGQE3BKJ1Y5L4M6,9F4DWXZZDLD,Q6L0 CEJ9TAAILK,KRY6CQCDRAAOW24UUI6JDDPUYXGU,Z1YNW5C4YQ 71HAXEID3BGTVYAR,OF07QEBF6F010U2W,GGSW3Y9DAQTATHCB 6JVD2N8WBYFDFZ,XAOUDQS2QR,08,SIMKWDWP4WSE89JYKBKFO NKAF,YKV,VGQIWVTA547PPW4WPD,A7POS38KK5SY,5VFOA1JO3 S09CTKAOTC5AKDZ,B31NS2AZMDZYOLS6FKJI8,S2X,FCCST6IP YTMZ2EE9AH52U8,OSSGSQ9SG04V4643XRBLVKTTT0E5,BUE85S KKFA6YYI48JI6,SQY,2RIS6ME136P5X,PMF9WQTP44C0WS,41D LW,CF,MJHV4VYTVZS2SMG2JFRFLCQGFP3QH,FB6X50MRIZ3,3P FL6,SDBNO5F19LMM8D51WX,EEZ1,JU41XW183FE6IUDFIRN,5O 0Z264TK87C7GOL2HAC00SQ8I,FPQ9JN2MM08VI48,AV2RKCNSJ SFLD7HESW,D8ATY8OK37QV,W3KBK69YI6VXUJ0EXL9Q,2JJJMV R2JGGV0,YEQDA39A2UUC6ATTMSF7GQG3MQPE,6SV8AEO0VKS9, WQUGPEUYNWLG0K4GSO916B48175,MVOBXA66HHOC,ZNTGDQ6CA W,4D4YXZTCAJAK6SMH2SS3OW,MOVEEI23R9TAU9ZIR0YXIS9BI 5,907SCT9JCF1X4JCKF,S593RQLG665UEOF,UGKICNABXNBDJD YKFKLX9,HDYHNN8S4VDP2P7QSPSKGB3K0L,QROKW2SY86LFF7A CE3R3R97,28DGI,P8FBSP7OCY9BN5Z05YK26BATVT010,1I7OI E5MVMANE,9Q9C3MAKRSRL2,CT486JMRPSSUNF,YGG2K5ZGBLS7 T395V79,26XF361LWSPLQ0UDXKIRYLB1AT,MW1NXTD12NHNHYP 11LMKQOSW,TKXCZP208OIQ5QNEHHUVAKTJ8,SYLOGQMK9T332V 6573GOG23FMYC,D1HPALEIQV9RJ8UQANN2ZQ,9MBBFWYE,R3AK EHLTQFXRCAFQQRIMOMZ0D,47KSCIZKBXQGJ03KTC0OZD3T1,KD T4XFRYAYBQZ9P2QY,UCCYPAZDGN6T566MWZ3W0EV,3UJFRNQ7F 60AVC7KKLK4H4Y,ZDM34UIX6VDGXPKVT63,JD,GLO0HIT,275I WH94L1DR,2RGDSO50H53K1NKNNV0D0VCEY,HUKVUM4OUDD6I0Y GN9S2J,45Z19,81GY,CJZMMTXY,T2OMCOMSYWJ3U,WUZ2OLM2Q W54NSE,NL58QHTMU80R5AG47I1,8GZPBY7BEQ,AE449LN4R2,U TW36XF7GV9DCYJCJ484X8C51RYLV9,W44K6NQ5N6N4N9YH,ETR 2ZF4WKUO27Y3KZTK0P,0QSCS76TTGKPNVPNSPY88,O886GOOZN HE76J969YC5JBYXLVA,1TWEY8X91G6UB8OR9W2YL4VIK7SA,I4 IH,ELQCG7ZF1K61WT80V,05FM0OP,R1S,0KG1K5Y9C3JS5RBS, OH4NJ5H,3NHD9Q00,MR6V2K4EZ5WQF7D,6ELX09O,JXEP,CL5J CNDDC,H9K,KPMAUWY6RA,L5PKKCXQJJHK4SKFOGZVH,WZYFWKG K17B9W7PA2M1R4FC,2ZAVUOI6O,LL50WZXVSERU2M2P8,BU0Y, 8AQ8YRQRWU28M90HU6W9FW4OIY5II,MSWBE,NPPRNH0GCMKJE0 L9X3NR9WRHWCKS6G,9YP9HNXQ,JO8XZQSG89J585GJ19JTX8Q, CT12XB1HSV,8NJX4M4YEMPT0ZSAL5G2,IAZW2TX057EVPEONEI 7KXKACMCN,S3D,6N5PDV5RC,9B3INDBI531D,4MNZUXAA,VKL2 AUFQVH,13YPXUT6TGLESA4KA6G37XUDDD3,6Z24,BY1GH,OEWP 46,5CGBIYSB9FG1U3TVBYSKG,SA9O357E,0QUOM2YBBKLDS5AD HN6,5DYZF1JGQCEC095,W247VJ6DLI5RWVKBKCLF,D5CFDNCTQ 1P7ELC7DURW4RCX,SBP0,SNA0BFSKPA72SXQ448F2GGDS,IYN4 EF5G120N651HKWJTU,G6JVS6EKH58OGT7P5B93F4V6MTC,AB98 V1TGBSZMMAQHHEU,TU0ZYQ2,ZVI,HNS,624RM7CSL0KW,QEFOC M91BBVWBJTGS,S,V83M7ZFUKWH,GIN74CJZ383ENK,0CK,VT05 XDVIWJQHSTJ4GVCFW9WRJT6MJ,DQDXNDUD64N21RXR8V,WIXLH PEM6SHY99AG,VM7P94XD7T04M,PO,5135Z3GVTMGF3HWE4,HZG 9PQMLBGBUCN0,FAJURGT6O8E,MYIMLIZDA,NEMBYSITYVQNBOU YN68VKBP4,XZB8NFN,1MHN99S1EWQYHW6SUEJP0WV,LN6GXGQW C5Y,4QPK4MISLAZRDVXNBI3MAO37QIYR,PV,NVDY02IM7V4YJ4 49ZT,MT24U9OC5BEE96DQ,XGAID8KTS7GP,HDVR0FXKP4B,WVD 8UBGKESD23F,AKKM6P13FU6Q2UOEC5W,2WACM83ELTG2L0JQF5 E,Q23AP0VWKOZ50PYWTN1RJH50UQG,03A380WSW596W2J,JEZE OKHYNKWLSKRQ45,S,VXWV73PQKD2W4FJAYFNO6,0LOI0B9ZLXH IEM24E5V0K,B42I7DSCPTAJGX6G2BO2V,I7KXGTG7DIT33VW,N YSWHYGK25G28RH3WW2JCOL5V,WEFDQUFV750C1LD0,O,YAAEVM GKIZJGOAWB7,I1O883JOAW27W,WGADJUZVQDWKI2CJG3EP,XNI ZKBYU,7FFTU84W7XS,L6AKM0SOLB4K,W0MCD7,A57QSMZ,G9I0 VKHAJS9XAZXDB,F7LB3I205DTA43,AOHVMUOPAJ,C4KMLNYDX3 1JANDOUMD,3YF6LR11QZTYFO4OO3RRD5NLX,6Q8,IS8YSYWM,C NRCDR68YQ5G7R1F10LW,0KTYYLWCAS1BU444BWKO8MX0AX,9KO TW6HBLD8P2YAAK9LC3XZP9,UVAIO7IA,8H9CX8CU6A6U8I7A47 XJIT,O7P5J4OYYJ8M7SMG,D4TYRG4ZIF7PCUEG6,V1WBTQIT21
brainFizzle 
Pingback: Oracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long strings | brainFizzle
Pingback: Optimized approach to addressing LISTAGG() and the ORA-01489 error | brainFizzle
Brilliant stuff! Thanks, just what I was needing. The 4000 limit was driving me nuts.
LikeLike
I wanted to mention, in case anyone comes here for the solution… In order for it not to sum every ID together, but each ID as its own group, I had to use a “partition by”.
So instead of:
SELECT id, value || ‘,’ value, SUM( LENGTH( value || ‘,’ ) ) OVER ( ORDER BY id ASC ) tot_length
I used:
SELECT id, value || ‘,’ value, SUM( LENGTH( value || ‘,’ ) ) OVER ( PARTITION BY id ORDER BY id ASC ) tot_length
LikeLike
this is not working in my case. i have a single column table with 1000 rows each having a employee_no of 6 characters.
TOT_LENGTH IN MY CASE WILL BE 7000.
Substr (employee_no, 1, length(employee_no) – (7000-4000) ) will result in blank
LikeLike
Hi, the listagg limit is 4000 bytes, NOT 4000 chars. That info is very pertinent if using multibyte characters.
LikeLike