sql - 将listagg函数限制为前4000个字符

Question

This question already has answers here:

LISTAGG function: “result of string concatenation is too long”

(12个答案)



Oracle - ORA-01489: result of string concatenation is too long [duplicate]

(1个答案)


5年前关闭。




我有一个使用 listagg函数的查询，以逗号分隔的字符串的形式获取所有行，最终将其发送到大文本框中。我收到以下异常:
ORA-01489: result of string concatenation is too long
我知道问题在于，正在运行的用于汇总数据的查询返回的行太多，以至于 listagg正在执行的字符串连接违反了4000个字符的限制。但是，对于我的用例，截断到前4000个字符是完全可以接受的。

如何修改 here的示例查询，以将“值”列限制为最多4000个字符？
SELECT LISTAGG(product_name, ', ') WITHIN
GROUP(
ORDER BY product_name DESC) "Product_Listing"
FROM products
您无法将 substr包装在调用 listagg' because listagg throws the exception before substr`周围。

我已经在SO上看到了很多关于如何绕过4000个字符的限制的问题，但并没有限制所得到的值。

最佳答案

12.2及以上
ON OVERFLOW选项可以轻松处理超过4000个字符:

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

11.2至12.1

分析函数可以生成字符串聚合的运行总长度。然后，内联 View 可以删除长度大于4000的任何值。

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

--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 Fiddle演示查询。

关于sql - 将listagg函数限制为前4000个字符，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28301443/

上一篇：lua - 使用 luarocks 安装 luasocket

下一篇：jsf-2 - 静默 FullAjaxExceptionHandler

Answer 1

12.2及以上
ON OVERFLOW选项可以轻松处理超过4000个字符:

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

11.2至12.1

分析函数可以生成字符串聚合的运行总长度。然后，内联 View 可以删除长度大于4000的任何值。

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

--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 Fiddle演示查询。