LISTAGG function: "result of string concatenation is too long"

I'm using Oracle SQL developer version 3.0.04. I attempted to use the function LISTAGG to group the data together..

    CREATE TABLE FINAL_LOG AS
    SELECT SESSION_DT, C_IP, CS_USER_AGENT,
    listagg(WEB_LINK, ' ')
        WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS"
        FROM webviews
        GROUP BY C_IP, CS_USER_AGENT, SESSION_DT
        ORDER BY SESSION_DT

However, I keep getting the error,

SQL Error: ORA-01489: result of string concatenation is too long

I'm pretty sure that the output may be more than 4000, since the WEB_LINK mentioned here is a concatenated value of url stem and url query.

Is there any way to go around it or is there any other alternative?

Solution 1:

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;

This will return a clob value, so no limit on rows.

Solution 2:

Since the aggregates string can be longer than 4000 bytes, you can't use the LISTAGG function. You could potentially create a user-defined aggregate function that returns a CLOB rather than a VARCHAR2. There is an example of a user-defined aggregate that returns a CLOB in the original askTom discussion that Tim links to from that first discussion.

Solution 3:

listagg got recently covered by the ISO SQL standard (SQL:2016). As part of that, it also got an on overflow clause, which is supported by Oracle 12cR2.

LISTAGG(<expression>, <separator> ON OVERFLOW …)

The on overflow clause supports a truncate option (as alternative to the default on overflow error behavior).

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

The optional defaults to three periods (...) and will be added as last element if truncation happens.

If with count is specified and truncation happens, the number of omitted values is put in brackets and appended to the result.

More about listagg's on overflow clause: http://modern-sql.com/feature/listagg