Oracle LISTAGG(), ORA-01489 – Aggregating using CLOBs to handle long strings

In a previous post, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, I wrote about long string concatenations exceeding the 32767 limit for VARCHAR2s. This article provided a query that limited the total length of the concatenation to cut itself off at 32767. This allows the user to use the advantages of LISTAGG() while returning as much data as possible.

The goal of this article is to provide an alternative that will make use of CLOBs to avoid character limits. Below is a user-defined aggregate function that is loosely based on askTom’s STRAGG() function. This function allows aggregation of very long strings, but note that this is an aggregate function, not an analytic function. It does not support the user-defined delimiter, WITHIN GROUP() clause and PARTITION BY clause that make LISTAGG() quite useful. The order in which strings are aggregated cannot be specified by the user.

SQL> CREATE OR REPLACE
  2  TYPE ClobAggregator
  3  AS OBJECT
  4  (
  5    aggregate_clob CLOB,
  6
  7    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  8      RETURN NUMBER,
  9    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 10      RETURN NUMBER,
 11    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number )
 12      RETURN NUMBER,
 13    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 14      RETURN NUMBER
 15  );
 16  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY ClobAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT ClobAggregator )
  6    RETURN NUMBER
  7    IS
  8      temp_clob CLOB;
  9    BEGIN
 10
 11      DBMS_LOB.CREATETEMPORARY( temp_clob, TRUE, DBMS_LOB.CALL );
 12
 13      sctx := ClobAggregator( temp_clob );
 14
 15      RETURN ODCIConst.Success;
 16
 17    END;
 18
 19    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ClobAggregator, value IN CLOB )
 20    RETURN NUMBER
 21    IS
 22    BEGIN
 23
 24      IF DBMS_LOB.GETLENGTH( self.aggregate_clob ) > 0 THEN
 25        DBMS_LOB.APPEND( self.aggregate_clob, ',' );
 26      END IF;
 27
 28      DBMS_LOB.APPEND( self.aggregate_clob, value );
 29
 30      RETURN ODCIConst.Success;
 31
 32    END;
 33
 34    MEMBER FUNCTION ODCIAggregateTerminate( self IN ClobAggregator, returnValue OUT CLOB, flags IN number)
 35    RETURN NUMBER
 36    IS
 37    BEGIN
 38
 39      returnValue := self.aggregate_clob;
 40
 41      RETURN ODCIConst.Success;
 42
 43    END;
 44
 45    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ClobAggregator, ctx2 IN ClobAggregator )
 46    RETURN NUMBER
 47    IS
 48    BEGIN
 49
 50      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 51
 52      RETURN ODCIConst.Success;
 53
 54    END;
 55
 56  END;
 57  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION CLOBAGG( input CLOB )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING ClobAggregator;
  6  /

Function created.

Below is a small example demonstrating how the function aggregates. This function requires every row to be concatenated to an ongoing CLOB, so performance can be limited for large data sets.

SQL> SELECT CLOBAGG( value )
  2  FROM clob_test;

CLOBAGG(VALUE)
------------------------------------------------------------
Sally,Bob,James,Amelia,Sam,David,Phillip,Patrick,Scott,Bill

As a note of interest, STRAGG() was originally introduced to fill a void in string aggregation prior to the introduction of the LISTAGG() function. It gained widespread use throughout the Oracle community.

Advertisements

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