您可以使用XMLAGG函数实现类似的功能：

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

这将返回一个LOB值，因此对行数没有限制。

由于聚合字符串可以超过4000个字节，因此不能使用LISTAGG函数。您可能会创建一个返回CLOB而不是VARCHAR2的用户定义聚合函数。有一个用户定义的聚合示例，它在原始的askTom讨论中返回一个CLOB，Tim从第一个讨论链接到该讨论。

listagg最近被ISO SQL标准(SQL：2016)涵盖。作为其中的一部分，它还获得了Oracle 12cR2支持的on overflow子句。

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

on overflow子句支持truncate选项(作为缺省on overflow error行为的替代)。

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

可选默认为三个句点(...)如果发生截断，将被添加为最后一个元素。

如果指定了WITH COUNT并发生截断，则省略的值数将放在括号中并附加到结果中。

有关listagg的on overflow子句的更多信息：http://modern-sql.com/feature/listagg

12cR2中添加的新功能是LISTAGG的ON OVERFLOW子句。包含此子句的查询将如下所示：

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

以上命令将输出限制为4000个字符，但不会抛出ORA-01489错误。

以下是ON OVERFLOW子句的一些附加选项：

• ON OVERFLOW TRUNCATE 'Contd..'：在字符串末尾显示'Contd..'(默认为...)
• ON OVERFLOW TRUNCATE ''：这将显示不带任何终止字符串的4000个字符。
• ON OVERFLOW TRUNCATE WITH COUNT：这将在终止字符之后的末尾显示字符总数。例如：‘...(5512)’
• ON OVERFLOW ERROR：如果您预计LISTAGG会失败并出现ORA-01489错误(无论如何这都是默认错误)。

LISTAGG Enhancements in 12c R2

您正在超过适用于LISTAGG的SQL限制4000字节。

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输出，以避免SQL 4000字节的限制。

例如,

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;

在公认的答案上再加上一句话。我遇到了类似的问题，最终使用了一个用户定义的函数，该函数返回lob而不是varchar2。以下是我的解决方案：

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;
/

现在，与其做

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

我必须这么做

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

我可以容忍将我的字段连接成多行，每行少于4000个字符的限制--执行了以下操作：

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)
;

管理LISTAGG中的溢出

我们可以使用数据库12c SQL模式匹配函数Match_Recognition来返回不超过限制的值列表。

示例代码和更多解释在下面的链接中。

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

在某些场景中，意图是获取所有不同的LISTAGG密钥，而溢出是由于LISTAGG连接所有密钥而导致的。

这里有一个小例子

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

如果组很大，重复的键很快就会达到允许的最大长度，您就会得到ORA-01489: result of string concatenation is too long。

遗憾的是，不支持LISTAGG( DISTINCT y, '; ')，但作为解决办法，可以使用LISTAGG忽略空值这一事实。使用ROW_NUMBER，我们将只考虑第一个键。

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

当然，在子查询中使用GROUP BY x,y也可以得到相同的结果。ROW_NUMBER的优点是可以使用所有其他聚合函数，如SUM(z)所示。

谢谢你的建议。我在连接几个字段时也遇到了同样的问题，但即使是xmlagg也帮不了我-我仍然得到了ORA-01489。经过几次尝试，我找到了原因和解决方案：

1.原因：我的xmlagg中有一个字段存储了大文本；
1.解决方案：应用to_clob()函数。

示例：

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

希望这对任何人都有帮助。

我们在这里使用Oracle LISTAGG解决了类似的问题。我们分组的内容一度超过了4K的限制，但这个问题很容易解决，方法是让第一个数据集聚合前15个项目，每个项目都有256K的限制。

更多信息：我们有项目，这些项目有变更单，而变更单又有解释。为什么数据库设置为接受256K大小限制的更改文本块尚不清楚，但这是设计限制之一。因此，将变更解释反馈到表中的应用程序在254K停止并插入，然后获得下一组文本，如果>254K，则生成另一行，依此类推。因此，我们有一个项目到变更单，1：1。然后我们将这些作为1：N用于解释。LISTAGG将所有这些串联在一起。我们有RMRKS_SN值，每个备注和/或每254K字符1个。

发现最大的RMRKS_SN是31，所以我做了第一个数据集拉出SN 0到15，第二个数据集16到30，最后一个数据集31到45--嘿，让我们计划有人给一些变更单增加很多解释！

在SQL报告中，Tablix绑定到第一个数据集。要获取其他数据，表达式如下：

=First(字段！NON_STD_TXT.Value，“DataSet_EXPLAN”)&First(字段！NON_STD_TXT.Value，“DS_EXPLAN_SN_16_TO_30”)&First(Fields！Non_STD_TXT.Value，“DS_EXPLAN_SN_31_TO_45”)

对于我们来说，由于安全限制，我们必须让DB Group创建函数等。因此，只要有一点创造力，我们就不必进行用户聚合或UDF。

如果您的应用程序有某种类型的SN可供聚合，则此方法应该有效。我不知道等同的TSQL是什么--我们很幸运地为这份报告与Oracle打交道，LISTAGG对它来说是一个天赐之物。

代码是：

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

而在其他两个数据集中，只需为From中的子查询选择LISTAGG：

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

从..。

WHERE RT.RMRKS_SN BETWEEN 31 AND 45

..。

..。诸若此类。

除了使用12c溢出之外，使用CLOB和substr也可以

Rtrim(DBMS_lob.substr(XMLAGG(XMLELEMENT(E，COLUMN_NAME，‘，’).EXTRACT(‘//Text()’)ORDER BY COLUMN_NAME).GetClobVal()，1000，1)，‘，’)

要实现聚合并处理XML/HTML内容和Unicode字符，请使用以下工具：

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

• XMLCAST将阻止对XML/HTML进行编码
• TO_NCLOB会将Unicode字符转换为“？”(不是很理想，但比查询中断要好！)

如果您只需要处理XML/HTML，而不需要担心Unicode字符，您可以使用

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

这种方法的唯一缺点是，您只能选择已分组的列和要聚合的列。如果要将结果与其他列一起包含，则需要将其放置在公用表表达式(CTE)中，并将其与Group by列联接到同一个表。