For very long lists, we need the return values to be CLOB
s. Alas, LISTAGG
can only return VARCHAR2
lists, but XML and JSON aggregate functions can return CLOB
s!
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
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'<'
or'>'
. - 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
JSON support in the Oracle database is pretty new. For full support of CLOB
s, 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_VALUE
to 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), '","', ',' ), '$[0]' 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!
Hi.
With Oracle SQL Developer 19.2.0.206 i have this error with the json generator query code:
00907. 00000 – “missing right parenthesis”
*Cause:
*Action:
Error en la línea: 17, columna: 5
Hi Fernando,
I don’t understand your error code. The JSON code in my blog post only has 16 lines, and your error message says line 17.
Also, the version of Oracle SQL Developer is not important. What is important is the version of the Oracle Database.
Could you tell me your precise Oracle Database version, and paste the code you ran in another comment?
Best regards,
Stew