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_Tthen 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: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
NUMBERdata type then only aNUMBERvalue 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 aNUMBERdoes 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:
fiddle