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
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.
.0334 secs per iteration
.1875 secs per iteration
The simplest solution, however, would be to set up an RESTful service using ORDS.
Another workaround involves a CASE statement.