LISTAGG 函数: “由于字符串连接的结果太长”

小开

最佳答案

由于聚合字符串可以长于4000字节，因此不能使用 LISTAGG函数。您可以创建返回 CLOB而不是 VARCHAR2的用户自定义聚合函数。有一个用户定义聚合的例子，它返回 Tim 从第一次讨论中链接到的汤姆讨论的原始问题中的 CLOB。

小开

我们能够在这里使用 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 (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”)

对我们来说，由于安全约束，我们必须让 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

...

诸如此类。

小开

你可以用 XMLAGG 函数完成类似的功能:

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

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

小开

补充已被接受的答案。我遇到了类似的问题，最终使用用户定义的函数返回 clob 而不是 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字节的 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 输出，以避免 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;

小开

管理 LISTAGG 的资金溢出

我们可以使用 Database 12c SQL 模式匹配函数 MATCH _ RECOGNIZE 返回一个不超过限制的值列表。

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

Https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_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 忽略 NULL。使用 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)所示。

小开

我可以容忍我的字段连接到多个行，每个行少于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最近被 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错误(无论如何都是默认的)。

12c R2中的 LISTAGG 增强

小开

如果没有使用 CLOB 和 subr 的12c 溢出，也可以使用

(XMLAGG (XMLELEMENT (E，column _ name,’,’) . EXTRACT (’//text ()’) ORDER BY column _ name) . GetClobVal () ，1000,1) ,’,’)

小开

谢谢你的建议。我有同样的问题时，连接几个字段，但即使 xmlagg也没有帮助我-我仍然得到了 ORA-01489。经过几次尝试，我找到了原因和解决办法:

原因: 其中一个字段在我的 xmlagg存储大文本;
解决方案: 应用 to_clob()函数。

例如:

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

希望这对大家有帮助。

小开

要完成聚合和处理 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

这种方法的唯一缺点是只能选择分组列和要聚合的列。如果希望将结果包含在其他列中，则需要将其放置在公共表表达式(Common Table Expression，CTE)中，并将其与 Group By 列连接到同一个表中。

小开

我使用的自定义函数命名为 clob_agg，简单地使用如下:

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

真正的例子:

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

输出是: 228887 = = > length of output:)

为了定义函数(在 sqlDeveloper、 dataGrip 或其他编辑器中使用此函数有时无法执行) :

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