SQL 将 listagg 函数限制为前 4000 个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28301443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Limit listagg function to first 4000 characters
提问by spots
I have a query that uses the listaggfunction to get all rows as a comma delimited string to ultimately be shipped to a big text box. I'm getting the following exception:
我有一个查询，它使用该listagg函数将所有行作为逗号分隔的字符串获取，最终发送到一个大文本框。我收到以下异常：
ORA-01489: result of string concatenation is too long
ORA-01489: result of string concatenation is too long
I know the problem is that the query being run to aggregate the data is returning so many rows that the string concatenation that listaggis doing violates the 4000 char limit. However, for my use case it's perfectly acceptable to truncate to the first 4000 characters.
我知道问题在于正在运行以聚合数据的查询返回的行太多，以至于listagg正在执行的字符串连接违反了 4000 个字符的限制。但是，对于我的用例，截断到前 4000 个字符是完全可以接受的。
How would I modify this example query from hereto limit the "value" column to be max 4000 characters?
我将如何从此处修改此示例查询以将“值”列限制为最多 4000 个字符？
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
You can't wrap substraround the call listagg' becauselistaggthrows the exception beforesubstr` ever gets called.
你不能环绕substr调用listagg' becauselistagg throws the exception beforesubstr` 曾经被调用。
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.
我在 SO 上看到了很多关于如何绕过 4000 个字符限制但不限制结果值的问题。
回答by Jon Heller
12.2 and above
12.2及以上
The ON OVERFLOWoption makes is easy to handle more than 4000 characters:
该ON OVERFLOW选项可以轻松处理 4000 多个字符：
select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;
11.2 to 12.1
11.2 到 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.
解析函数可以生成字符串聚合的运行总长度。然后内联视图可以删除长度大于 4000 的任何值。
In a real query you may need to add a partition byto the analytic functions, to only count per some group.
在实际查询中，您可能需要将 a 添加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
Here's a SQL Fiddledemonstrating the query.
这是一个演示查询的SQL Fiddle。
