SQL LISTAGG 函数：“字符串连接的结果太长”

Question

提问by user1874311

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

我正在使用 Oracle SQL 开发人员版本 3.0.04。我试图使用该函数LISTAGG将数据组合在一起..

    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.

我很确定输出可能会超过4000，因为这里提到的WEB_LINK是url stem和url query的串联值。

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

有什么办法可以绕过它还是有其他选择？

Answer 1

采纳答案by Justin Cave

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

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

Answer 2

回答by Ankur Bhutani

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.

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

Answer 3

回答by Markus Winand

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

listagg最近被 ISO SQL 标准 (SQL:2016) 覆盖。作为其中的一部分，它还有一个on overflow子句，Oracle 12cR2 支持该子句。

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

The on overflowclause supports a truncateoption (as alternative to the default on overflow errorbehavior).

该on overflow子句支持一个truncate选项（作为默认on overflow error行为的替代）。

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.

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

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

更多关于listagg's 的on overflow条款：http: //modern-sql.com/feature/listagg

Answer 4

回答by Lalit Kumar B

You are exceeding the SQL limitof 4000 bytes which applies to LISTAGGas well.

您超出了同样适用于 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

As a workaround, you could use XMLAGG.

作为一种解决方法，您可以使用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 columnswhich itself have 4000 bytes, then you can concatenate the XMLAGG output of each column to avoid the SQL limit of 4000 bytes.

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

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 5

回答by Kaushik Nayak

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

在12cR2添加了一个新功能是ON OVERFLOW的条款LISTAGG。包含此子句的查询如下所示：

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-01489error.

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

These are some of the additional options of ON OVERFLOWclause:

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

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 LISTAGGto fail with the ORA-01489error ( Which is default anyway ).

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

12c R2 中的 LISTAGG 增强功能

Answer 6

回答by Ferdie

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

我可以容忍我的字段连接成多行，每行少于 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)
;

Answer 7

回答by Abhishek Maurya

Managing overflows in LISTAGG

管理 LISTAGG 中的溢出

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

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

Example code and more explanation in below link.

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

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

Answer 8

回答by Chiranjib

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:

添加到已接受的答案。我遇到了类似的问题，最终使用了返回 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;
/

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 9

回答by Marmite Bomber

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

在某些情况下，目的是获取所有DISTINCT LISTAGG键，而溢出是由 LISTAGG 连接所有键这一事实引起的。

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.

如果组很大，重复的键很快就会达到允许的最大长度，你会得到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.

不幸的是，不支持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

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

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

Answer 10

回答by Volkov Maxim

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

谢谢你的建议。连接多个字段时我遇到了同样的问题，但甚至xmlagg没有帮助我 - 我仍然得到 ORA-01489。经过多次尝试，我找到了原因和解决方案：

Cause: one of fields in my xmlaggstores large text;
Solution: apply to_clob()function.

原因：我xmlagg商店中的字段之一是大文本；
解决方法：应用to_clob()函数。

Example:

例子：

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

Hope this help anybody.

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

SQL LISTAGG 函数：“字符串连接的结果太长”

提问by user1874311

采纳答案by Justin Cave

回答by Ankur Bhutani

回答by Markus Winand

回答by Lalit Kumar B

回答by Kaushik Nayak

回答by Ferdie

回答by Abhishek Maurya

回答by Chiranjib

回答by Marmite Bomber

回答by Volkov Maxim

相关推荐

最近更新

标签

SQL LISTAGG 函数：“字符串连接的结果太长”

提问by user1874311

采纳答案by Justin Cave

回答by Ankur Bhutani

回答by Markus Winand

回答by Lalit Kumar B

回答by Kaushik Nayak

回答by Ferdie

回答by Abhishek Maurya

回答by Chiranjib

回答by Marmite Bomber

回答by Volkov Maxim

相关推荐

SQL 在sql中使用datepart()得到一个两位数怎么办？

SQL 我应该在数据库连接字符串中设置最大池大小吗？如果我不这样做会怎样？

如何将选择中的所有列与 SQL Server 连接

SQL 触发器 - 如何获取更新的值？

相关推荐

最近更新

标签