Truncating LISTAGG prior to 12.2

I've been following the LISTAGG problem for a while because I find the function useful.

The problem being this error:
ORA-01489: result of string concatenation is too long

It relates to when the result set is over 4000 characters, eg:

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(str,', ') within group (order by null)
from data

Ten rows of 400 characters equals 4000, add the concatenation operator and you get the error.
If the second parameter was null, this would actually be within the 4000 limit.

There are improvements in 12.2 to handle this overflow.

with data as (select rpad('x',400, 'x') str from dual connect by level <=11)
select listagg(str, ',' on overflow truncate '...' with count) within group (order by null)
from data

The output would end
...*********,...(8985)

But if you're still in 12.1 or earlier, you need other options

One solution is a custom LISTAGG function to return a CLOB.

You could make a decision on the incoming length, which probably isn't always practical, or you can concatenate only the first x rows using a CASE statement

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(case when rownum < 5 then str end
              ,', ') within group (order by null)
            ||'First 5 of '||count(*)||' shown.' as str
from data;

Which ends the output with
...********First 5 of 10 shown.

I suspect this will do for a lot of scenarios. Ordering is up to you.

I don't remember encountering this idea in the context of LISTAGG, but it seems nice and simple.

LiveSQL example:
https://livesql.oracle.com/apex/livesql/file/content_EHQAVVYH6J2XMN5VHTFJYT3Y8.html

LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

Update 2019: Connor now his this code in his Github.

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search after the fact.

A little tip if you do find it - don't attempt to copy the code from the translated version of the page. It's amazing how many syntax issues were introduced by the translator, and logical issues that I wouldn't have noticed had I not performed a diff after things went wrong.

For instance, the "aggregate" keyword here vanished:
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/

Thank's Carsten for a useful solution using custom aggregate functions, an element of PL/SQL I'm yet to master.

It's certainly faster than the XML solution, here is the throughput difference from 100 iterations of each.
-- listagg_clob
3.34 secs 
.0334 secs per iteration
-- xmlagg
18.75 secs 
.1875 secs per iteration

The simplest solution, however, would be to set up an RESTful service using ORDS.

Another workaround involves a CASE statement.