Limit listagg function to first 4000 characters [duplicate]

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 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 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_namesfrom(    --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 Fiddle demonstrating the query.