Limit listagg function to first 4000 characters [duplicate]

Question

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

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.

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

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

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.

Answer 1

12.2 and above

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

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

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.

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 bytes 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. Use LENGTHB in case of multibyte characters.
        sum(lengthb(product_name) + 2) over (order by product_name) running_length,
        sum(lengthb(product_name) + 2) over () total_length
    from products
    order by product_name
)
where running_length <= 3996;

Here's a db<>fiddle demonstrating the query.