By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or replace PROCEDURE ETA_JSON_SERIALIZE (
json_in IN JSON_ELEMENT_T,
can_str IN OUT VARCHAR2,
object_key IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF json_in.is_Object() THEN
DECLARE
l_object JSON_OBJECT_T := TREAT(json_in AS JSON_OBJECT_T);
l_keys JSON_KEY_LIST := l_object.get_Keys();
BEGIN
FOR i IN 1 .. l_keys.COUNT LOOP
can_str := can_str || '"'||UPPER(l_keys(i))||'"';
ETA_JSON_SERIALIZE(l_object.get(l_keys(i)), can_str, l_keys(i));
END LOOP;
END;
ELSIF json_in.is_Array() THEN
DECLARE
l_array JSON_ARRAY_T := TREAT(json_in AS JSON_ARRAY_T);
BEGIN
FOR i IN 0 .. l_array.get_size - 1 LOOP
IF object_key IS NOT NULL THEN
can_str := can_str || '"'||UPPER(object_key)||'"';
END IF;
ETA_JSON_SERIALIZE(l_array.get(i), can_str);
END LOOP;
END;
ELSIF json_in.is_Scalar() THEN
IF json_in.is_String() THEN
can_str := can_str || json_in.to_String();
ELSIF json_in.is_Number() THEN
can_str := can_str || '"' || TO_CHAR(json_in.to_Number(), 'FM999999990.00') || '"';
END IF;
END IF;
END;
SELECT * FROM USER_ERRORS;
DECLARE
can_str VARCHAR2(32767);
l_doc CLOB := '{
"taxTotals": [
{
"taxType": "T1",
"amount": 477.54
},
{
"taxType": "T2",
"amount": 365.47
},
{
"taxType": "T3",
"amount": 60.00
},
{
"taxType": "T4",
"amount": 75.99
},
{
"taxType": "T5",
"amount": 214.71
},
{
"taxType": "T6",
"amount": 120.00
}
],
"totalAmount": 5191.50,
"extraDiscountAmount": 5.00,
"totalItemsDiscountAmount": 14.00
}';
BEGIN
ETA_JSON_SERIALIZE(JSON_ELEMENT_T.parse( l_doc ), can_str);
1 rows affected
dbms_output:
"TAXTOTALS""TAXTOTALS""TAXTYPE""T1""AMOUNT""477.54""TAXTOTALS""TAXTYPE""T2""AMOUNT""365.47""TAXTOTALS""TAXTYPE""T3""AMOUNT""60.00""TAXTOTALS""TAXTYPE""T4""AMOUNT""75.99""TAXTOTALS""TAXTYPE""T5""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T6""AMOUNT""120.00""TOTALAMOUNT""5191.50""EXTRADISCOUNTAMOUNT""5.00""TOTALITEMSDISCOUNTAMOUNT""14.00"