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

Posted by on Apr 23, 2012 in 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”

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.

SELECT 
   table_row_id,
   DBMS_XMLGEN.CONVERT(EXTRACT(xmltype('<?xml version="1.0"?><document>'||XMLAGG(XMLTYPE('<V>'|| DBMS_XMLGEN.CONVERT(data_value)|| '</V>')).getclobval()||'</document>'), '/document/V/text()') .getclobval(),1) AS data_value
FROM (
   SELECT 1 table_row_id,'abcdefg>' data_value FROM dual
   UNION ALL
   SELECT 1 table_row_id,'hijklmn' data_value FROM dual)
GROUP BY
   table_row_id;

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

DBMS_XMLGEN.CONVERT(data_value)

‘abcdefg>’ ‘becomes abcdef&lt;g’

Next we create an XMLELEMENT for each row and convert the result to XML data type

XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')

Then we do the XML aggregation

XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()

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

DBMS_XMLGEN.CONVERT(EXTRACT(xmltype(''||XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()||''), '/document/V/text()') .getclobval(),1)

Most Popular Posts

One Comment

  • Rahul on Nov 28, 2012

    Thanks this really helped me

Leave Reply