Oracle SQL Listagg for Clobs

In Oracle the listagg function is limited to 4000 characters. If the resulting string exceeds 4000 characters then an error is raised. Example:

with 
    data as (
        -- Generates:
        -- - Numbers each 20 digits in length 
        -- - Alpha numeric strings 20 characters in length
        select 
            trunc(dbms_random.value() * power(10, 20)) num,
            dbms_random.string('x', 20) str
        from dual
        connect by level <= 10000
    )
select 
    listagg(d.num, ',') within group (order by d.num asc) all_nums,
    listagg(d.str, ',') within group (order by d.num asc) all_strs
from data d
;

-- Raises
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
Error at Line: 0 Column: 0

You can control whether an error is raised (on overflow error) or truncate (on overflow truncate ...) the string using the listagg_overflow_clause. The truncate solution still doesn't solve the issue if you have a lot of content you want to concatenate.

An alternative approach is to use json_arrayagg along with the returning clob option. This trick only works in the following conditions:

• Numbers, dates, etc (i.e. structured date types)

• For strings they have some known format and do not contain quotes or commas

The example above can be converted from listagg to json_arrayagg:

with 
    data as (
        select 
            trunc(dbms_random.value() * power(10, 20)) num,
            dbms_random.string('x', 20) str
        from dual
        connect by level <= 10000
    )
select 
    json_arrayagg(d.num returning clob) json_array_nums,
    json_arrayagg(d.str returning clob) json_array_strs
from data d
;

-- Returns 
-- [16390942703930704741,72925476174569629179,98810344353460307813,...]
-- ["OJV24DVTF6EIF80F76NE","CY6T5L8S3BZW2CN0CHJY",...]

The result still contains the square brackets and quotes around the strings. To remove them to just have comma delimited list we can use regular expressions (can also do with a few replace statements):

with 
    data as (
        select 
            trunc(dbms_random.value() * power(10, 20)) num,
            dbms_random.string('x', 20) str
        from dual
        connect by level <= 10000
    )
select 
    -- Note: the order of the square brackets in the regexp string: []["] matters
    -- as SQL doesn't require escaping strings within square bracket operators
    regexp_replace(json_arrayagg(d.num returning clob), '[]["]', '') nums,
    regexp_replace(json_arrayagg(d.str returning clob), '[]["]', '') strs
from data d
;

-- Returns 
-- 16390942703930704741,72925476174569629179,98810344353460307813,... 
-- OJV24DVTF6EIF80F76NE,CY6T5L8S3BZW2CN0CHJY,...