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.