将listagg函数限制为前4000个字符 [英] Limit listagg function to first 4000 characters

问题描述

我有一个查询，该查询使用函数将所有行作为逗号分隔的字符串，最终将其运送到大文本框中.我收到以下异常:

I have a query that uses the listagg function to get all rows as a comma delimited string to ultimately be shipped to a big text box. I'm getting the following exception:

ORA-01489: result of string concatenation is too long

我知道问题是正在运行以汇总数据的查询返回了太多行，以致listagg正在执行的字符串连接违反了4000个字符的限制.但是，对于我的用例，截断到前4000个字符是完全可以接受的.

I know the problem is that the query being run to aggregate the data is returning so many rows that the string concatenation that listagg is doing violates the 4000 char limit. However, for my use case it's perfectly acceptable to truncate to the first 4000 characters.

我将如何从此处修改此示例查询，以限制值列中最多可包含4000个字符?

How would I modify this example query from here to limit the "value" column to be max 4000 characters?

SELECT LISTAGG(product_name, ', ') WITHIN GROUP( ORDER BY product_name DESC) "Product_Listing" FROM products

SELECT LISTAGG(product_name, ', ') WITHIN GROUP( ORDER BY product_name DESC) "Product_Listing" FROM products

您永远无法将substr包裹在呼叫listagg' because listagg throws the exception before substr`周围.

You can't wrap substr around the call listagg' becauselistaggthrows the exception beforesubstr` ever gets called.

关于SO如何绕过4000个字符的限制，而不是限制结果值，我已经看到了很多问题.

I've seen a lot of question on SO about how to get around the 4000 character limit, but not to limit the resulting value.

推荐答案

12.2及更高版本

ON OVERFLOW选项可以轻松处理4000多个字符:

12.2 and above

The ON OVERFLOW option makes is easy to handle more than 4000 characters:

select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;

11.2至12.1

解析函数可以生成字符串聚合的运行总长度.然后，内联视图可以删除长度大于4000的所有值.

11.2 to 12.1

An analytic function can generate a running total length of the string aggregation. Then an inline view can remove any values where the length is greater than 4000.

在真实查询中，您可能需要在分析函数中添加partition by，以便仅按某个组进行计数.

In a real query you may need to add a partition by to the analytic functions, to only count per some group.

--The first 4000 characters of PRODUCT_NAME.
select
    --Save a little space for a ' ...' to imply that there is more data not shown.
    case when max(total_length) > 3996 then
        listagg(product_name, ', ') within group (order by product_name)||
            ' ...'
    else
        listagg(product_name, ', ') within group (order by product_name)
    end product_names
from
(
    --Get names and count lengths.
    select
        product_name,
        --Add 2 for delimiters.
        sum(length(product_name) + 2) over (order by product_name) running_length,
        sum(length(product_name) + 2) over () total_length
    from products
    order by product_name
)
where running_length <= 3996

这是 SQL小提琴，用于演示查询.

这篇关于将listagg函数限制为前4000个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！