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;