Oracle LISTAGG() — ORA-01489: result of string concatenation is too long

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
Advertisements

4 thoughts on “Oracle LISTAGG() — ORA-01489: result of string concatenation is too long

  1. Pingback: Oracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long strings | brainFizzle

  2. Pingback: Optimized approach to addressing LISTAGG() and the ORA-01489 error | brainFizzle

  3. 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

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s