LISTAGG function: "result of string concatenation is too long"

Question

I'm using Oracle SQL developer version 3.0.04. I attempted to use the function LISTAGG to group the data together..

    CREATE TABLE FINAL_LOG AS
    SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    listagg(WEB_LINK, ' ')
        WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
        FROM webviews
        GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
        ORDER BY SESSION_DT

However, I keep getting the error,

SQL Error: ORA-01489: result of string concatenation is too long

I'm pretty sure that the output may be more than 4000, since the WEB_LINK mentioned here is a concatenated value of url stem and url query.

Is there any way to go around it or is there any other alternative?

Answer 1

I am using custom function named as clob_agg using simply like:

select clob_agg(*detail_column*) 
from *table* 
group by *group_column*

Real example:

select length(clob_agg(x||'')) fullList
from (select level as x
      from dual
      connect by level < 40000)

The output is: 228887 ==> length of output :)

For define function (Use this in sqlDeveloper, dataGrip or some another editors can't execute sometimes):

CREATE OR REPLACE TYPE t_clob_agg AS OBJECT
(
    g_string clob,

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_clob_agg IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        sctx := t_clob_agg(NULL);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_clob_agg,
                                         value IN clob)
        RETURN NUMBER IS
    BEGIN
        -- Concatenate string only when not already existing in the list (=unique)
        SELF.g_string := self.g_string || ',' || value;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_clob_agg,
                                           returnValue OUT clob,
                                           flags IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_clob_agg,
                                       ctx2 IN t_clob_agg)
        RETURN NUMBER IS
    BEGIN
        SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
        RETURN ODCIConst.Success;
    END;


END;
/
SHOW ERRORS



CREATE OR REPLACE FUNCTION clob_agg(p_input clob)
    RETURN clob
    PARALLEL_ENABLE AGGREGATE USING t_clob_agg;
/
SHOW ERRORS

Answer 2

To accomplish aggregation AND handle XML / HTML content AND Unicode characters, use the following:

SELECT uuid, XMLCAST(XMLAGG(XMLELEMENT(E, TO_NCLOB(text),'') ORDER BY uuid) AS NCLOB) AS text 
GROUP BY UUID

• XMLCAST will prevent the XML / HTML from being encoded
• TO_NCLOB will turn Unicode characters to "?" (not ideal, but better than the query breaking!)

If you only need to handle XML / HTML and don't need to worry about Unicode characters, you can use

SELECT uuid, XMLCAST(XMLAGG(XMLELEMENT(E, TO_CLOB(text),'') ORDER BY uuid) AS CLOB) AS text 
GROUP BY UUID

The only drawback with this approach is that you will only be able to select the grouped columns and the column you're aggregating. This will need to be placed in a Common Table Expression (CTE) and join it to the same table with the Group By column if you want to include the results with other columns.

Answer 3

You can accomplish similar functionality with the XMLAGG function:

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;

This will return a clob value, so no limit on rows.

Answer 4

Thank you for advices. I had the same problem when concatenate several fields, but even xmlagg not helped me - I still got the ORA-01489. After several attempts I found the cause and solution:

1. Cause: one of fields in my xmlagg stores large text;
2. Solution: apply to_clob() function.

Example:

rtrim(xmlagg(xmlelement(t, t.field1 ||'|'|| 
                           t.field2 ||'|'|| 
                           t.field3 ||'|'|| 
                           to_clob(t.field4),'; ').extract('//text()')).GetClobVal(),',')

Hope this help anybody.

Answer 5

Short of using 12c overflow using the CLOB and substr will also work

rtrim(dbms_lob.substr(XMLAGG(XMLELEMENT(E,column_name,',').EXTRACT('//text()') ORDER BY column_name).GetClobVal(),1000,1),',')

Answer 6

A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG. The query including this clause would look like:

SELECT pid, LISTAGG(Desc, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

The above will restrict the output to 4000 characters but will not throw the ORA-01489 error.

These are some of the additional options of ON OVERFLOW clause:

• ON OVERFLOW TRUNCATE 'Contd..' : This will display 'Contd..' at the end of string (Default is ... )
• ON OVERFLOW TRUNCATE '' : This will display the 4000 characters without any terminating string.
• ON OVERFLOW TRUNCATE WITH COUNT : This will display the total number of characters at the end after the terminating characters. Eg:- '...(5512)'
• ON OVERFLOW ERROR : If you expect the LISTAGG to fail with the ORA-01489 error ( Which is default anyway ).

LISTAGG Enhancements in 12c R2

Answer 7

listagg got recently covered by the ISO SQL standard (SQL:2016). As part of that, it also got an on overflow clause, which is supported by Oracle 12cR2.

LISTAGG(<expression>, <separator> ON OVERFLOW …)

The on overflow clause supports a truncate option (as alternative to the default on overflow error behavior).

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

The optional defaults to three periods (...) and will be added as last element if truncation happens.

If with count is specified and truncation happens, the number of omitted values is put in brackets and appended to the result.

More about listagg's on overflow clause: http://modern-sql.com/feature/listagg

Answer 8

I could tolerate my field concatenated into multiple rows each less than the 4000 character limit - did the following:

with PRECALC as (select 
                 floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
                 from MY_TABLE)
select LISTAGG(MY_COLUMN,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN)
from MY_TABLE, PRECALC
group by floor(rownum/MAX_FIELD_LENGTH)
;

Answer 9

In some scenarios the intention is to get all DISTINCT LISTAGG keys and the overflow is caused by the fact that LISTAGG concatenates ALL keys.

Here is a small example

create table tab as
select 
  trunc(rownum/10) x,
  'GRP'||to_char(mod(rownum,4)) y,
  mod(rownum,10) z
 from dual connect by level < 100;


select  
 x,
 LISTAGG(y, '; ') WITHIN GROUP (ORDER BY y) y_lst
from tab
group by x;


        X Y_LST                                                            
---------- ------------------------------------------------------------------
         0 GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3               
         1 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         2 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         3 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         4 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         5 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         6 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         7 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         
         8 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3         
         9 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3         

If the groups are large, the repeated keys reach quickly the allowed maximal length and you get the ORA-01489: result of string concatenation is too long.

Unfortunately there is no support for LISTAGG( DISTINCT y, '; ') but as a workaround the fact can be used that LISTAGG ignores NULLs. Using the ROW_NUMBER we will consider only the first key.

with rn as (
select x,y,z,
row_number() over (partition by x,y order by y) rn
from tab
)
select  
 x,
 LISTAGG( case when rn = 1 then y end, '; ') WITHIN GROUP (ORDER BY y) y_lst,
 sum(z) z 
from rn
group by x
order by x;

         X Y_LST                                       Z
---------- ---------------------------------- ----------
         0 GRP0; GRP1; GRP2; GRP3             45 
         1 GRP0; GRP1; GRP2; GRP3             45 
         2 GRP0; GRP1; GRP2; GRP3             45 
         3 GRP0; GRP1; GRP2; GRP3             45 
         4 GRP0; GRP1; GRP2; GRP3             45 
         5 GRP0; GRP1; GRP2; GRP3             45 
         6 GRP0; GRP1; GRP2; GRP3             45 
         7 GRP0; GRP1; GRP2; GRP3             45 
         8 GRP0; GRP1; GRP2; GRP3             45 
         9 GRP0; GRP1; GRP2; GRP3             45

Of course the same result may be reached using GROUP BY x,y in the subquery. The advantage of ROW_NUMBER is that all other aggregate functions may be used as illustrated with SUM(z).

Answer 10

Managing overflows in LISTAGG

We can use the Database 12c SQL pattern matching function, MATCH_RECOGNIZE, to return a list of values that does not exceed limit.

Example code and more explanation in below link.

https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg

Answer 11

You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well.

SQL> SELECT listagg(text, ',') WITHIN GROUP (
  2  ORDER BY NULL)
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /
SELECT listagg(text, ',') WITHIN GROUP (
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

As a workaround, you could use XMLAGG.

For example,

SQL> SET LONG 2000000
SQL> SET pagesize 50000
SQL> SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
  2                     ).GetClobVal(),',') very_long_text
  3  FROM
  4    (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
  5    )
  6  /

VERY_LONG_TEXT
--------------------------------------------------------------------------------
one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen
,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty-one,twenty-two,twenty
-three,twenty-four,twenty-five,twenty-six,twenty-seven,twenty-eight,twenty-nine,
thirty,thirty-one,thirty-two,thirty-three,thirty-four,thirty-five,thirty-six,thi
rty-seven,thirty-eight,thirty-nine,forty,forty-one,forty-two,forty-three,forty-f
our,forty-five,forty-six,forty-seven,forty-eight,forty-nine,fifty,fifty-one,fift
y-two,fifty-three,fifty-four,fifty-five,fifty-six,fifty-seven,fifty-eight,fifty-
nine,sixty,sixty-one,sixty-two,sixty-three,sixty-four,sixty-five,sixty-six,sixty
-seven,sixty-eight,sixty-nine,seventy,seventy-one,seventy-two,seventy-three,seve
nty-four,seventy-five,seventy-six,seventy-seven,seventy-eight,seventy-nine,eight
y,eighty-one,eighty-two,eighty-three,eighty-four,eighty-five,eighty-six,eighty-s
even,eighty-eight,eighty-nine,ninety,ninety-one,ninety-two,ninety-three,ninety-f
our,ninety-five,ninety-six,ninety-seven,ninety-eight,ninety-nine,one hundred,one
 hundred one,one hundred two,one hundred three,one hundred four,one hundred five
,one hundred six,one hundred seven,one hundred eight,one hundred nine,one hundre
d ten,one hundred eleven,one hundred twelve,one hundred thirteen,one hundred fou
rteen,one hundred fifteen,one hundred sixteen,one hundred seventeen,one hundred
eighteen,one hundred nineteen,one hundred twenty,one hundred twenty-one,one hund
red twenty-two,one hundred twenty-three,one hundred twenty-four,one hundred twen
ty-five,one hundred twenty-six,one hundred twenty-seven,one hundred twenty-eight
,one hundred twenty-nine,one hundred thirty,one hundred thirty-one,one hundred t
hirty-two,one hundred thirty-three,one hundred thirty-four,one hundred thirty-fi
ve,one hundred thirty-six,one hundred thirty-seven,one hundred thirty-eight,one
hundred thirty-nine,one hundred forty,one hundred forty-one,one hundred forty-tw
o,one hundred forty-three,one hundred forty-four,one hundred forty-five,one hund
red forty-six,one hundred forty-seven,one hundred forty-eight,one hundred forty-
nine,one hundred fifty,one hundred fifty-one,one hundred fifty-two,one hundred f
ifty-three,one hundred fifty-four,one hundred fifty-five,one hundred fifty-six,o
ne hundred fifty-seven,one hundred fifty-eight,one hundred fifty-nine,one hundre
d sixty,one hundred sixty-one,one hundred sixty-two,one hundred sixty-three,one
hundred sixty-four,one hundred sixty-five,one hundred sixty-six,one hundred sixt
y-seven,one hundred sixty-eight,one hundred sixty-nine,one hundred seventy,one h
undred seventy-one,one hundred seventy-two,one hundred seventy-three,one hundred
 seventy-four,one hundred seventy-five,one hundred seventy-six,one hundred seven
ty-seven,one hundred seventy-eight,one hundred seventy-nine,one hundred eighty,o
ne hundred eighty-one,one hundred eighty-two,one hundred eighty-three,one hundre
d eighty-four,one hundred eighty-five,one hundred eighty-six,one hundred eighty-
seven,one hundred eighty-eight,one hundred eighty-nine,one hundred ninety,one hu
ndred ninety-one,one hundred ninety-two,one hundred ninety-three,one hundred nin
ety-four,one hundred ninety-five,one hundred ninety-six,one hundred ninety-seven
,one hundred ninety-eight,one hundred ninety-nine,two hundred,two hundred one,tw
o hundred two,two hundred three,two hundred four,two hundred five,two hundred si
x,two hundred seven,two hundred eight,two hundred nine,two hundred ten,two hundr
ed eleven,two hundred twelve,two hundred thirteen,two hundred fourteen,two hundr
ed fifteen,two hundred sixteen,two hundred seventeen,two hundred eighteen,two hu
ndred nineteen,two hundred twenty,two hundred twenty-one,two hundred twenty-two,
two hundred twenty-three,two hundred twenty-four,two hundred twenty-five,two hun
dred twenty-six,two hundred twenty-seven,two hundred twenty-eight,two hundred tw
enty-nine,two hundred thirty,two hundred thirty-one,two hundred thirty-two,two h
undred thirty-three,two hundred thirty-four,two hundred thirty-five,two hundred
thirty-six,two hundred thirty-seven,two hundred thirty-eight,two hundred thirty-
nine,two hundred forty,two hundred forty-one,two hundred forty-two,two hundred f
orty-three,two hundred forty-four,two hundred forty-five,two hundred forty-six,t
wo hundred forty-seven,two hundred forty-eight,two hundred forty-nine

---

If you want to concatenate multiple columns which itself have 4000 bytes, then you can concatenate the XMLAGG output of each column to avoid the SQL limit of 4000 bytes.

For example,

WITH DATA AS
  ( SELECT 1 id, rpad('a1',4000,'*') col1, rpad('b1',4000,'*') col2 FROM dual
  UNION
  SELECT 2 id, rpad('a2',4000,'*') col1, rpad('b2',4000,'*') col2 FROM dual
  )
SELECT ID,
       rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',')
       || 
       rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',') 
       AS very_long_text
FROM DATA
GROUP BY ID
ORDER BY ID;

Answer 12

We were able to solve a similar issue here using Oracle LISTAGG. There was a point where what we were grouping on exceeded the 4K limit but this was easily solved by having the first dataset take the first 15 items to aggregate, each of which have a 256K limit.

More info: We have projects, which have change orders, which in turn have explanations. Why the database is set up to take change text in chunks of 256K limits is not known but its one of the design constraints. So the application that feeds change explanations into the table stops at 254K and inserts, then gets the next set of text and if > 254K generates another row, etc. So we have a project to a change order, a 1:1. Then we have these as 1:n for explanations. LISTAGG concatenates all these. We have RMRKS_SN values, 1 for each remark and/or for each 254K of characters.

The largest RMRKS_SN was found to be 31, so I did the first dataset pulling SN 0 to 15, the 2nd dataset 16 to 30 and the last dataset 31 to 45 -- hey, let's plan on someone adding a LOT of explanation to some change orders!

In the SQL report, the Tablix ties to the first dataset. To get the other data, here's the expression:

=First(Fields!NON_STD_TXT.Value, "DataSet_EXPLAN") & First(Fields!NON_STD_TXT.Value, "ds_EXPLAN_SN_16_TO_30") & First(Fields!NON_STD_TXT.Value, "ds_EXPLAN_SN_31_TO_45")

For us, we have to have DB Group create functions, etc. because of security constraints. So with a bit of creativity, we didn't have to do a User Aggregate or a UDF.

If your application has some sort of SN to aggregate by, this method should work. I don't know what the equivalent TSQL is -- we're fortunate to be dealing with Oracle for this report, for which LISTAGG is a Godsend.

The code is:

SELECT
LT.C_O_NBR AS LT_CO_NUM,
RT.C_O_NBR AS RT_CO_NUM,
LT.STD_LN_ITM_NBR, 
RT.NON_STD_LN_ITM_NBR,
RT.NON_STD_PRJ_NBR, 
LT.STD_PRJ_NBR, 
NVL(LT.PRPSL_LN_NBR, RT.PRPSL_LN_NBR) AS PRPSL_LN_NBR,
LT.STD_CO_EXPL_TXT AS STD_TXT,
LT.STD_CO_EXPLN_T, 
LT.STD_CO_EXPL_SN, 
RT.NON_STD_CO_EXPLN_T,
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

FROM ...

    WHERE RT.RMRKS_SN BETWEEN 0 AND 15

GROUP BY 
    LT.C_O_NBR,
    RT.C_O_NBR,
    ...

And in the other 2 datasets just select the LISTAGG only for the subqueries in the FROM:

SELECT
LISTAGG(RT.RMRKS_TXT_FLD, '') 
    WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT

FROM ...

WHERE RT.RMRKS_SN BETWEEN 31 AND 45

...

... and so on.

Answer 13

Adding on to the accepted answer. I ran into a similar problem and ended up using a user defined function that returned clob instead of varchar2. Here's my solution:

CREATE OR REPLACE TYPE temp_data FORCE AS OBJECT
(
    temporary_data NVARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE temp_data_table FORCE AS TABLE OF temp_data;
/

CREATE OR REPLACE FUNCTION my_agg_func (p_temp_data_table IN temp_data_table, p_delimiter IN NVARCHAR2)
RETURN CLOB IS
  l_string CLOB;
BEGIN
  FOR i IN p_temp_data_table.FIRST .. p_temp_data_table.LAST LOOP
    IF i != p_temp_data_table.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_temp_data_table(i).temporary_data;
  END LOOP;
  RETURN l_string;
END my_agg_func;
/

Now, instead of doing

LISTAGG(column_to_aggregate, '#any_delimiter#') WITHIN GROUP (ORDER BY column_to_order_by)

I have to do this

my_agg_func (
    cast(
        collect(
            temp_data(column_to_aggregate)
            order by column_to_order_by
        ) as temp_data_table
    ),
    '#any_delimiter#'
)

Answer 14

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.