LISTAGG with CLOB? String aggregation exceeding 4000 characters with XMLAGG.
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.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 |
DBMS_XMLGEN.CONVERT(data_value) |
‘abcdefg>’ ‘becomes abcdef>g’
Next we create an XMLELEMENT for each row and convert the result to XML data type
|
1 |
XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '') |
Then we do the XML aggregation
|
1 |
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.
|
1 |
DBMS_XMLGEN.CONVERT(EXTRACT(xmltype('<!--?xml version="1.0"?-->'||XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()||''), '/document/V/text()') .getclobval(),1) |
