Oracle String Aggregation - Aggregated string as CLOB

** Be warned - thought this sql aggregates strings into a clob (large strings) it also, by design of the XML functions it uses, escapes all special characters! (e.g. & will be escaped to & the xml safe equivalent)**

I needed to aggregate rows returned into a single string. Yes i know, easy! (Even easier in 11gR2 with stragg, but we're currently on 11gR1).

The resultant value exceeded the varchar2(4000) allowable limit.

Here is a method to aggregate rows with the resulting string being presented as a CLOB and the method (simple TRIM) to clean up the ending ',' from the string:

SELECT  t.TABLESPACE_NAME
,COUNT(*) CNT
,trim(TRIM(TRAILING ',' FROM 
         EXTRACT(
                DISTINCT XMLAGG(XMLTYPE('<V>'|| t.table_name||',</V>') ORDER BY                                                        t.table_name),'/V/text()'
).GETCLOBVAL()
)
) TBLS

FROM sys.user_tables T
GROUP BY t.TABLESPACE_NAME
ORDER BY 2 DESC