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

Question

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?

Answer 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.