您超过了4000字节的SQL限制,也适用于LISTAGG.

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

作为解决方法,您可以使用XMLAGG.

例如,

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

如果要连接多个本身有4000个字节的列,则可以连接每列的XMLAGG输出以避免4000字节的SQL限制.

例如,

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;