For very long lists, we need the return values to be
LISTAGG can only return
VARCHAR2 lists, but XML and JSON aggregate functions can return
As an exercise, I’ll start from
ALL_OBJECTS: I want to make a list of all fully qualified object names for each object type. To start with, a simple subquery:
with data as ( select object_type, owner ||'.'|| object_name obj from all_objects ) select * from data; OBJECT_TYPE OBJ INDEX SYS.I_FILE#_BLOCK# INDEX SYS.I_OBJ3 INDEX SYS.I_TS1 INDEX SYS.I_CON1 ...
XML functions work on the
XMLTYPE data type, which has practically no length limitations. To make my list, I have to go through three steps:
- Make an XML fragment out of each object.
This step will escape any special characters such as
- Aggregate the XML fragments.
- Extract the text from the aggregated XML data.
This step will unescape the special characters.
I have to handle myself the comma that separates the elements of the list.
with data as ( select object_type, owner ||'.'|| object_name obj from all_objects ) select object_type, SUBSTR( xmlcast( xmlagg( xmlelement(O, ',' || obj) order by obj ) AS CLOB ), 2 ) qualified_objects from data group by object_type order by object_type;
On my system with about 78000 objects, this takes 3 seconds and a bit.
JSON support in the Oracle database is pretty new. For full support of
CLOBs, we need versions 18c or later (or a patched version 12.2, but I don’t know the precise version number).
The steps are a little different with JSON:
- I can aggregate the data directly into a JSON array.
This escapes any special characters like double quotes, newlines, etc.
- To turn the multiple array items into one (which is my list), I replace
","(which separates the array items) with my separator.
- I then use
JSON_VALUEto extract the list from the array.
This unescapes any character that needs it.
with data as ( select object_type, owner ||'.'|| object_name obj from all_objects ) select object_type, json_value( replace( json_arrayagg(obj order by obj returning clob), '","', ',' ), '$' returning clob ) qualified_objects from data group by object_type order by object_type;
Instead of 3+ seconds, this code takes a bit over 1 second, so three times faster.
As a reminder, JSON is great for going the other way: split the items of a list into a table. See Splitting Strings: a New Champion!