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

Uli Bethke Oracle

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"

Teach me Big Data to Advance my Career

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.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.