[SOLVED] LISTAGG function: "result of string concatenation is too long"

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

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.