Thanks for reply. Pkg body is having lot of errors while compiling. Is there any specific db version this is compatible with?
Errors:
Project: C:\Users\mukidimx\AppData\Roaming\SQL Developer\system19.2.1.247.2212\o.sqldeveloper\projects\IdeConnections#PMDEV.jpr
Package Body APPS.XXLN_XUTL_JSON@PMDEV
Error(10,3): PL/SQL: Item ignored
Error(11,18): PLS-00201: identifier 'JSON_ELEMENT_T' must be declared
Error(47,17): PLS-00201: identifier 'JSON_OBJECT_T' must be declared
Error(47,17): PL/SQL: Item ignored
Error(48,17): PLS-00201: identifier 'JSON_ARRAY_T' must be declared
Error(48,17): PL/SQL: Item ignored
Error(49,17): PLS-00201: identifier 'JSON_KEY_LIST' must be declared
Error(49,17): PL/SQL: Item ignored
Error(53,5): PL/SQL: Statement ignored
Error(54,15): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(147,16): PLS-00201: identifier 'JSON_OBJECT_T' must be declared
Error(147,16): PL/SQL: Item ignored
Error(148,16): PLS-00201: identifier 'JSON_ARRAY_T' must be declared
Error(148,16): PL/SQL: Item ignored
Error(163,9): PL/SQL: Statement ignored
Error(163,16): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(169,11): PL/SQL: Statement ignored
Error(169,18): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(172,13): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(172,13): PL/SQL: Statement ignored
Warning(1,1): Only first 20 issues are reported

Hi, how can i resolve the error ORA-01704 if the XML that i want to convert to a JSON is longer than 4000 characters?.
Thanks

Hi,
I have a 12 oracle BD and i can do a group by JSON with:
SELECT JSON_OBJECT('VW_SIGMA_MOTIVOS' VALUE JSON_ARRAYAGG(
JSON_OBJECT(mot.tipo_accion VALUE JSON_ARRAYAGG(
JSON_OBJECT('value' VALUE mot.TIPO_MOTIVO,
'label' VALUE mot.DESC_MOTIVO
) ORDER BY mot.DESC_MOTIVO asc RETURNING CLOB ) RETURNING CLOB ) RETURNING CLOB ) RETURNING CLOB )
FROM VW_SIGMA_MOTIVOS mot
GROUP BY mot.tipo_accion;
like this:
{
"VW_SIGMA_MOTIVOS": [
{
"ATN3": [
{
"value": "ATN3",
"label": "Investigación de accidente no traumático"
}
]
},
{
"HAB1": [
{
"value": "2017-5",
"label": "Accidentes Máquinas"
},
{
"value": "2018-5",
"label": "Accidentes Máquinas"
},
{
"value": "2017-4",
"label": "Amianto"
},
{
"value": "2019-3",
"label": "Amianto"
},
{
"value": "2021-10",
"label": "Campaña análisis ATG no investigados 2021"
}
]
},
{
"SIN": [
{
"value": "SIN",
"label": "Actuación singular"
}
]
}
]
}
I wanna do the same in a 11g BD. Is there any way to do that query in an 11 BD or to obtain a group by XML and then convert it to a JSON?. I have the XML query but i need it in an unic XML.

SELECT XMLELEMENT (tipo_accion,
XMLAGG(
XMLELEMENT("ROW",
XMLELEMENT("value", tipo_motivo),
XMLELEMENT("label", desc_motivo)
)).getClobVal()
) FROM (SELECT * FROM VW_SIGMA_MOTIVOS ORDER BY tipo_motivo) GROUP BY tipo_accion

https://github.com/mbleron/JSONUtilities

Please provide link to type declarations. The package body won't compile without them:-
Package Body XUTL_JSON compiled

LINE/COL ERROR
--------- -------------------------------------------------------------
10/3 PL/SQL: Item ignored
11/18 PLS-00201: identifier 'JSON_ELEMENT_T' must be declared
47/17 PLS-00201: identifier 'JSON_OBJECT_T' must be declared
47/17 PL/SQL: Item ignored
48/17 PLS-00201: identifier 'JSON_ARRAY_T' must be declared
48/17 PL/SQL: Item ignored
49/17 PLS-00201: identifier 'JSON_KEY_LIST' must be declared
49/17 PL/SQL: Item ignored
53/5 PL/SQL: Statement ignored
54/15 PLS-00320: the declaration of the type of this expression is incomplete or malformed
147/16 PLS-00201: identifier 'JSON_OBJECT_T' must be declared
147/16 PL/SQL: Item ignored
148/16 PLS-00201: identifier 'JSON_ARRAY_T' must be declared

This will convert wellformed XML to JSON:

SELECT XMLTOJSON(XMLTYPE('<xml>
<firstName>John</firstName>
<lastName>Smith</lastName>
<age>25</age>
<address>
<streetAddress>21 2nd Street</streetAddress>
<city>New York</city>
<state>NY</state>
<postalCode>10021</postalCode>
</address>
<phoneNumber>
<type>home</type>
<number>212 555-1234</number>
</phoneNumber>
<phoneNumber>
<type>fax</type>
<number>646 555-4567</number>
</phoneNumber>
</xml>')) FROM dual;

though it is not listed here - it seems to be working.

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html#Oracle%C2%AE-Database

might be an undocumented function/feature.

demo@PDB1> SELECT XMLTOJSON(XMLTYPE('<xml>
  2  <firstName>John</firstName>
  3  <lastName>Smith</lastName>
  4  <age>25</age>
  5  <address>
  6  <streetAddress>21 2nd Street</streetAddress>
  7  <city>New York</city>
  8  <state>NY</state>
  9  <postalCode>10021</postalCode>
 10  </address>
 11  <phoneNumber>
 12  <type>home</type>
 13  <number>212 555-1234</number>
 14  </phoneNumber>
 15  <phoneNumber>
 16  <type>fax</type>
 17  <number>646 555-4567</number>
 18  </phoneNumber>
 19  </xml>')) FROM dual;

XMLTOJSON(XMLTYPE('<XML><FIRSTNAME>JOHN</FIRSTNAME><LASTNAME>SMITH</LASTNAME><AGE>25</AGE><ADDRESS><STREETADDRESS>212NDSTREET</STREETADDRESS><CITY>NEWYORK</CITY><STATE>NY</STATE><PO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"xml":{"address":{"city":"New York","postalCode":10021,"state":"NY","streetAddress":"21 2nd Street"},"age":25,"firstName":"John","lastName":"Smith","phoneNumber":[{"number":"212 55
5-1234","type":"home"},{"number":"646 555-4567","type":"fax"}]}}


demo@PDB1> desc xmltojson
ERROR:
ORA-04043: object xmltojson does not exist