I am using Oracle Apex 22.2 and Oracle Database XE 21c on CentOS7. I am traversing all the nested elements of JSON data with the procedure shown below. However when I get the value of a number, the number loses its original format. For example, instead of getting 100.0, I get 100. How can I reserve the original format of the number? As you can see in my code below, I am using to_String to get value of the number. This is was one of my attempts to reserve the original number format. I have also tried with TO_CHAR & TO_NUMBER() . All give the same output. Your help is really appreciated.
Procedure Code
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_NUMBER(json_in.to_String) || '"';
END IF;
END IF;
END;
Sample Code - PL/SQL Block
SET SERVEROUTPUT ON
DECLARE
can_str VARCHAR2(32767);
l_doc CLOB := '{
"issuer": {
"address": {
"branchID": "1",
"country": "EG",
"governate": "Cairo",
"regionCity": "Nasr City",
"street": "580 Clementina Key",
"buildingNumber": "Bldg. 0",
"postalCode": "68030",
"floor": "1",
"room": "123",
"landmark": "7660 Melody Trail",
"additionalInformation": "beside Townhall"
},
"type": "B",
"id": "113317713",
"name": "Issuer Company"
},
"receiver": {
"address": {
"country": "EG",
"governate": "Egypt",
"regionCity": "Mufazat al Ismlyah",
"street": "580 Clementina Key",
"buildingNumber": "Bldg. 0",
"postalCode": "68030",
"floor": "1",
"room": "123",
"landmark": "7660 Melody Trail",
"additionalInformation": "beside Townhall"
},
"type": "B",
"id": "313717919",
"name": "Receiver"
},
"documentType": "I",
"documentTypeVersion": "0.9",
"dateTimeIssued": "2020-10-27T23:59:59Z",
"taxpayerActivityCode": "4620",
"internalID": "IID1",
"purchaseOrderReference": "P-233-A6375",
"purchaseOrderDescription": "purchase Order description",
"salesOrderReference": "1231",
"salesOrderDescription": "Sales Order description",
"proformaInvoiceNumber": "SomeValue",
"payment": {
"bankName": "SomeValue",
"bankAddress": "SomeValue",
"bankAccountNo": "SomeValue",
"bankAccountIBAN": "",
"swiftCode": "",
"terms": "SomeValue"
},
"delivery": {
"approach": "SomeValue",
"packaging": "SomeValue",
"dateValidity": "2020-09-28T09:30:10Z",
"exportPort": "SomeValue",
"countryOfOrigin": "EG",
"grossWeight": 10.50,
"netWeight": 20.50,
"terms": "SomeValue"
},
"invoiceLines": [
{
"description": "Computer1",
"itemType": "GPC",
"itemCode": "10001774",
"unitType": "EA",
"quantity": 5,
"internalCode": "IC0",
"salesTotal": 947.00,
"total": 2969.89,
"valueDifference": 7.00,
"totalTaxableFees": 817.42,
"netTotal": 880.71,
"itemsDiscount": 5.00,
"unitValue": {
"currencySold": "EUR",
"amountEGP": 189.40,
"amountSold": 10.00,
"currencyExchangeRate": 18.94
},
"discount": {
"rate": 7,
"amount": 66.29
},
"taxableItems": [
{
"taxType": "T1",
"amount": 272.07,
"subType": "T1",
"rate": 14.00
},
{
"taxType": "T2",
"amount": 208.22,
"subType": "T2",
"rate": 12
},
{
"taxType": "T3",
"amount": 30.00,
"subType": "T3",
"rate": 0.00
},
{
"taxType": "T4",
"amount": 43.79,
"subType": "T4",
"rate": 5.00
},
{
"taxType": "T5",
"amount": 123.30,
"subType": "T5",
"rate": 14.00
},
{
"taxType": "T6",
"amount": 60.00,
"subType": "T6",
"rate": 0.00
},
{
"taxType": "T7",
"amount": 88.07,
"subType": "T7",
"rate": 10.00
},
{
"taxType": "T8",
"amount": 123.30,
"subType": "T8",
"rate": 14.00
},
{
"taxType": "T9",
"amount": 105.69,
"subType": "T9",
"rate": 12.00
},
{
"taxType": "T10",
"amount": 88.07,
"subType": "T10",
"rate": 10.00
},
{
"taxType": "T11",
"amount": 123.30,
"subType": "T11",
"rate": 14.00
},
{
"taxType": "T12",
"amount": 105.69,
"subType": "T12",
"rate": 12.00
},
{
"taxType": "T13",
"amount": 88.07,
"subType": "T13",
"rate": 10.00
},
{
"taxType": "T14",
"amount": 123.30,
"subType": "T14",
"rate": 14.00
},
{
"taxType": "T15",
"amount": 105.69,
"subType": "T15",
"rate": 12.00
},
{
"taxType": "T16",
"amount": 88.07,
"subType": "T16",
"rate": 10.00
},
{
"taxType": "T17",
"amount": 88.07,
"subType": "T17",
"rate": 10.00
},
{
"taxType": "T18",
"amount": 123.30,
"subType": "T18",
"rate": 14.00
},
{
"taxType": "T19",
"amount": 105.69,
"subType": "T19",
"rate": 12.00
},
{
"taxType": "T20",
"amount": 88.07,
"subType": "T20",
"rate": 10.00
}
]
},
{
"description": "Computer2",
"itemType": "GPC",
"itemCode": "10003752",
"unitType": "EA",
"quantity": 7,
"internalCode": "IC0",
"salesTotal": 662.90,
"total": 2226.61,
"valueDifference": 6.00,
"totalTaxableFees": 621.51,
"netTotal": 652.90,
"itemsDiscount": 9.00,
"unitValue": {
"currencySold": "EUR",
"amountEGP": 94.70,
"amountSold": 5.00,
"currencyExchangeRate": 18.94
},
"discount": {
"rate": 0,
"amount": 10.00
},
"taxableItems": [
{
"taxType": "T1",
"amount": 205.47,
"subType": "T1",
"rate": 14.00
},
{
"taxType": "T2",
"amount": 157.25,
"subType": "T2",
"rate": 12
},
{
"taxType": "T3",
"amount": 30.00,
"subType": "T3",
"rate": 0.00
},
{
"taxType": "T4",
"amount": 32.20,
"subType": "T4",
"rate": 5.00
},
{
"taxType": "T5",
"amount": 91.41,
"subType": "T5",
"rate": 14.00
},
{
"taxType": "T6",
"amount": 60.00,
"subType": "T6",
"rate": 0.00
},
{
"taxType": "T7",
"amount": 65.29,
"subType": "T7",
"rate": 10.00
},
{
"taxType": "T8",
"amount": 91.41,
"subType": "T8",
"rate": 14.00
},
{
"taxType": "T9",
"amount": 78.35,
"subType": "T9",
"rate": 12.00
},
{
"taxType": "T10",
"amount": 65.29,
"subType": "T10",
"rate": 10.00
},
{
"taxType": "T11",
"amount": 91.41,
"subType": "T11",
"rate": 14.00
},
{
"taxType": "T12",
"amount": 78.35,
"subType": "T12",
"rate": 12.00
},
{
"taxType": "T13",
"amount": 65.29,
"subType": "T13",
"rate": 10.00
},
{
"taxType": "T14",
"amount": 91.41,
"subType": "T14",
"rate": 14.00
},
{
"taxType": "T15",
"amount": 78.35,
"subType": "T15",
"rate": 12.00
},
{
"taxType": "T16",
"amount": 65.29,
"subType": "T16",
"rate": 10.00
},
{
"taxType": "T17",
"amount": 65.29,
"subType": "T17",
"rate": 10.00
},
{
"taxType": "T18",
"amount": 91.41,
"subType": "T18",
"rate": 14.00
},
{
"taxType": "T19",
"amount": 78.35,
"subType": "T19",
"rate": 12.00
},
{
"taxType": "T20",
"amount": 65.29,
"subType": "T20",
"rate": 10.00
}
]
}
],
"totalDiscountAmount": 76.29,
"totalSalesAmount": 1609.90,
"netAmount": 1533.61,
"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
},
{
"taxType": "T7",
"amount": 153.36
},
{
"taxType": "T8",
"amount": 214.71
},
{
"taxType": "T9",
"amount": 184.04
},
{
"taxType": "T10",
"amount": 153.36
},
{
"taxType": "T11",
"amount": 214.71
},
{
"taxType": "T12",
"amount": 184.04
},
{
"taxType": "T13",
"amount": 153.36
},
{
"taxType": "T14",
"amount": 214.71
},
{
"taxType": "T15",
"amount": 184.04
},
{
"taxType": "T16",
"amount": 153.36
},
{
"taxType": "T17",
"amount": 153.36
},
{
"taxType": "T18",
"amount": 214.71
},
{
"taxType": "T19",
"amount": 184.04
},
{
"taxType": "T20",
"amount": 153.36
}
],
"totalAmount": 5191.50,
"extraDiscountAmount": 5.00,
"totalItemsDiscountAmount": 14.00
}';
BEGIN
ETA_JSON_SERIALIZE(JSON_ELEMENT_T.parse( l_doc ), can_str);
DBMS_OUTPUT.PUT_LINE(can_str);
END;
/
Output
"ISSUER""ADDRESS""BRANCHID""1""COUNTRY""EG""GOVERNATE""Cairo""REGIONCITY""Nasr City""STREET""580 Clementina Key""BUILDINGNUMBER""Bldg. 0""POSTALCODE""68030""FLOOR""1""ROOM""123""LANDMARK""7660 Melody Trail""ADDITIONALINFORMATION""beside Townhall""TYPE""B""ID""113317713""NAME""Issuer Company""RECEIVER""ADDRESS""COUNTRY""EG""GOVERNATE""Egypt""REGIONCITY""Mufazat al Ismlyah""STREET""580 Clementina Key""BUILDINGNUMBER""Bldg. 0""POSTALCODE""68030""FLOOR""1""ROOM""123""LANDMARK""7660 Melody Trail""ADDITIONALINFORMATION""beside Townhall""TYPE""B""ID""313717919""NAME""Receiver""DOCUMENTTYPE""I""DOCUMENTTYPEVERSION""0.9""DATETIMEISSUED""2020-10-27T23:59:59Z""TAXPAYERACTIVITYCODE""4620""INTERNALID""IID1""PURCHASEORDERREFERENCE""P-233-A6375""PURCHASEORDERDESCRIPTION""purchase Order description""SALESORDERREFERENCE""1231""SALESORDERDESCRIPTION""Sales Order description""PROFORMAINVOICENUMBER""SomeValue""PAYMENT""BANKNAME""SomeValue""BANKADDRESS""SomeValue""BANKACCOUNTNO""SomeValue""BANKACCOUNTIBAN""""SWIFTCODE""""TERMS""SomeValue""DELIVERY""APPROACH""SomeValue""PACKAGING""SomeValue""DATEVALIDITY""2020-09-28T09:30:10Z""EXPORTPORT""SomeValue""COUNTRYOFORIGIN""EG""GROSSWEIGHT""10.5""NETWEIGHT""20.5""TERMS""SomeValue""INVOICELINES""INVOICELINES""DESCRIPTION""Computer1""ITEMTYPE""GPC""ITEMCODE""10001774""UNITTYPE""EA""QUANTITY""5""INTERNALCODE""IC0""SALESTOTAL""947""TOTAL""2969.89""VALUEDIFFERENCE""7""TOTALTAXABLEFEES""817.42""NETTOTAL""880.71""ITEMSDISCOUNT""5""UNITVALUE""CURRENCYSOLD""EUR""AMOUNTEGP""189.4""AMOUNTSOLD""10""CURRENCYEXCHANGERATE""18.94""DISCOUNT""RATE""7""AMOUNT""66.29""TAXABLEITEMS""TAXABLEITEMS""TAXTYPE""T1""AMOUNT""272.07""SUBTYPE""T1""RATE""14""TAXABLEITEMS""TAXTYPE""T2""AMOUNT""208.22""SUBTYPE""T2""RATE""12""TAXABLEITEMS""TAXTYPE""T3""AMOUNT""30""SUBTYPE""T3""RATE""0""TAXABLEITEMS""TAXTYPE""T4""AMOUNT""43.79""SUBTYPE""T4""RATE""5""TAXABLEITEMS""TAXTYPE""T5""AMOUNT""123.3""SUBTYPE""T5""RATE""14""TAXABLEITEMS""TAXTYPE""T6""AMOUNT""60""SUBTYPE""T6""RATE""0""TAXABLEITEMS""TAXTYPE""T7""AMOUNT""88.07""SUBTYPE""T7""RATE""10""TAXABLEITEMS""TAXTYPE""T8""AMOUNT""123.3""SUBTYPE""T8""RATE""14""TAXABLEITEMS""TAXTYPE""T9""AMOUNT""105.69""SUBTYPE""T9""RATE""12""TAXABLEITEMS""TAXTYPE""T10""AMOUNT""88.07""SUBTYPE""T10""RATE""10""TAXABLEITEMS""TAXTYPE""T11""AMOUNT""123.3""SUBTYPE""T11""RATE""14""TAXABLEITEMS""TAXTYPE""T12""AMOUNT""105.69""SUBTYPE""T12""RATE""12""TAXABLEITEMS""TAXTYPE""T13""AMOUNT""88.07""SUBTYPE""T13""RATE""10""TAXABLEITEMS""TAXTYPE""T14""AMOUNT""123.3""SUBTYPE""T14""RATE""14""TAXABLEITEMS""TAXTYPE""T15""AMOUNT""105.69""SUBTYPE""T15""RATE""12""TAXABLEITEMS""TAXTYPE""T16""AMOUNT""88.07""SUBTYPE""T16""RATE""10""TAXABLEITEMS""TAXTYPE""T17""AMOUNT""88.07""SUBTYPE""T17""RATE""10""TAXABLEITEMS""TAXTYPE""T18""AMOUNT""123.3""SUBTYPE""T18""RATE""14""TAXABLEITEMS""TAXTYPE""T19""AMOUNT""105.69""SUBTYPE""T19""RATE""12""TAXABLEITEMS""TAXTYPE""T20""AMOUNT""88.07""SUBTYPE""T20""RATE""10""INVOICELINES""DESCRIPTION""Computer2""ITEMTYPE""GPC""ITEMCODE""10003752""UNITTYPE""EA""QUANTITY""7""INTERNALCODE""IC0""SALESTOTAL""662.9""TOTAL""2226.61""VALUEDIFFERENCE""6""TOTALTAXABLEFEES""621.51""NETTOTAL""652.9""ITEMSDISCOUNT""9""UNITVALUE""CURRENCYSOLD""EUR""AMOUNTEGP""94.7""AMOUNTSOLD""5""CURRENCYEXCHANGERATE""18.94""DISCOUNT""RATE""0""AMOUNT""10""TAXABLEITEMS""TAXABLEITEMS""TAXTYPE""T1""AMOUNT""205.47""SUBTYPE""T1""RATE""14""TAXABLEITEMS""TAXTYPE""T2""AMOUNT""157.25""SUBTYPE""T2""RATE""12""TAXABLEITEMS""TAXTYPE""T3""AMOUNT""30""SUBTYPE""T3""RATE""0""TAXABLEITEMS""TAXTYPE""T4""AMOUNT""32.2""SUBTYPE""T4""RATE""5""TAXABLEITEMS""TAXTYPE""T5""AMOUNT""91.41""SUBTYPE""T5""RATE""14""TAXABLEITEMS""TAXTYPE""T6""AMOUNT""60""SUBTYPE""T6""RATE""0""TAXABLEITEMS""TAXTYPE""T7""AMOUNT""65.29""SUBTYPE""T7""RATE""10""TAXABLEITEMS""TAXTYPE""T8""AMOUNT""91.41""SUBTYPE""T8""RATE""14""TAXABLEITEMS""TAXTYPE""T9""AMOUNT""78.35""SUBTYPE""T9""RATE""12""TAXABLEITEMS""TAXTYPE""T10""AMOUNT""65.29""SUBTYPE""T10""RATE""10""TAXABLEITEMS""TAXTYPE""T11""AMOUNT""91.41""SUBTYPE""T11""RATE""14""TAXABLEITEMS""TAXTYPE""T12""AMOUNT""78.35""SUBTYPE""T12""RATE""12""TAXABLEITEMS""TAXTYPE""T13""AMOUNT""65.29""SUBTYPE""T13""RATE""10""TAXABLEITEMS""TAXTYPE""T14""AMOUNT""91.41""SUBTYPE""T14""RATE""14""TAXABLEITEMS""TAXTYPE""T15""AMOUNT""78.35""SUBTYPE""T15""RATE""12""TAXABLEITEMS""TAXTYPE""T16""AMOUNT""65.29""SUBTYPE""T16""RATE""10""TAXABLEITEMS""TAXTYPE""T17""AMOUNT""65.29""SUBTYPE""T17""RATE""10""TAXABLEITEMS""TAXTYPE""T18""AMOUNT""91.41""SUBTYPE""T18""RATE""14""TAXABLEITEMS""TAXTYPE""T19""AMOUNT""78.35""SUBTYPE""T19""RATE""12""TAXABLEITEMS""TAXTYPE""T20""AMOUNT""65.29""SUBTYPE""T20""RATE""10""TOTALDISCOUNTAMOUNT""76.29""TOTALSALESAMOUNT""1609.9""NETAMOUNT""1533.61""TAXTOTALS""TAXTOTALS""TAXTYPE""T1""AMOUNT""477.54""TAXTOTALS""TAXTYPE""T2""AMOUNT""365.47""TAXTOTALS""TAXTYPE""T3""AMOUNT""60""TAXTOTALS""TAXTYPE""T4""AMOUNT""75.99""TAXTOTALS""TAXTYPE""T5""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T6""AMOUNT""120""TAXTOTALS""TAXTYPE""T7""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T8""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T9""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T10""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T11""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T12""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T13""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T14""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T15""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T16""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T17""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T18""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T19""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T20""AMOUNT""153.36""TOTALAMOUNT""5191.5""EXTRADISCOUNTAMOUNT""5""TOTALITEMSDISCOUNTAMOUNT""14"
Output Instances
How to make it reserve the original number format?
So "EXTRADISCOUNTAMOUNT""5.00"instead of"EXTRADISCOUNTAMOUNT""5"`
OR
"NETWEIGHT""20.50" instead of "NETWEIGHT""20.5"
OR
"TOTALITEMSDISCOUNTAMOUNT""14.00" instead of "TOTALITEMSDISCOUNTAMOUNT""14"
OR
"RATE""0.00" instead of "RATE""0"
You cannot.
Once you have parsed the JSON then Oracle has converted it to JSON_ELEMENT_T then there is no documented method that allows you to extract the underlying JSON text for that element so you can only use the serialization methods:
Serialization
Serialization is the inverse of the parse function. The serialization operation takes the in-memory representation of the JSON data and prints it to a string. The serialization functions and procedures are:
MEMBER FUNCTION to_String RETURN VARCHAR2 MEMBER FUNCTION to_Number RETURN NUMBER MEMBER FUNCTION to_Date RETURN DATE MEMBER FUNCTION to_Timestamp RETURN TIMESTAMP MEMBER FUNCTION to_Boolean RETURN BOOLEAN MEMBER FUNCTION to_Clob RETURN CLOB MEMBER FUNCTION to_Blob RETURN BLOB MEMBER PROCEDURE to_Clob(c IN OUT CLOB) MEMBER PROCEDURE to_Blob(c IN OUT BLOB)
As noted in the documentation, "The serialization operation takes the in-memory representation of the JSON data and ...". Therefore if the parser converts the JSON to a NUMBER data type then only a NUMBER value will be stored and the formatting of the original JSON text is NOT stored. Since 10.00 is exactly the same value as 10 and a NUMBER does not store formatting then you can only retrieve the value 10 and not 10.00.
If you want to get the original text then you will have to use a different parser that lets you extract the underlying JSON text.
Alternatively, if all the numbers want to be formatted to 2 decimal places then you could use:
can_str := can_str || '"' || TO_CHAR(json_in.to_Number(), 'FM9999990D00') || '"';