LISTAGG with CLOB? String aggregation exceeding 4000 characters with XMLAGG.

April 23, 2012

We all know that the Oracle LISTAGG function does not support the CLOB datatype. In my opinion this is a severe limitation of LISTAGG (and BTW also of PIVOT and UNPIVOT etc.).
So what are your options? One option is to create your own user defined aggregate function. I found such an example on the Oracle forums.
However, if you want to use pure SQL to achieve the same, we can leverage the XMLAGG SQL/XML functionality built into the Oracle database.
As per documentation: “You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements”

Below is an example to demonstrate how this works. For demonstration purposes I don’t exceed the 4K character limit in the example below. However, I guarantee you that it works with >4K strings as well.

Let’s have a closer look at the nested stuff in this query.
In a first step we escape the data into its XML equivalent

‘abcdefg>’ ‘becomes abcdef>g’
Next we create an XMLELEMENT for each row and convert the result to XML data type

Then we do the XML aggregation

In a last step we create a well formed XML document, extract the text value from the XML elements and unescape the text.