JSON Parsing using PLSQL - Oracle Database 19c PLSQL Development
Hello,
I have following code where I am having syntax error issue when appending array children to json object. Any documentation and online help I have searched (and I have searched extensively) only gives examples with static values. I need to append arrays using plsql cursor values as shown below.
Please advise what is the correct way of appending cursor values to json arrays.
Below is my code.
FOR i IN getHdrRec LOOP -- Create JSON payload l_json.put('BillToCustomerNumber',i.bill_to_account_id); l_json.put('BillToCustomerName',i.bill_to_account_name); l_json.put('BillToSite',i.bill_to_party_site_number); l_json.put('DueDate',i.due_date); l_json.put('BusinessUnit',i.company_id); l_json.put('CrossReference',i.document_id); l_json.put('TransactionDate',i.trx_date); l_json.put('CreationDate',i.creation_date); l_json.put('ShipToCustomerNumber',i.ship_to_account_id); l_json.put('ShipToSite',i.ship_to_party_site_number); l_json.put('TransactionSource','Equipment Rental'); l_json.put('TransactionType','Invoice'); l_json.put('InvoiceCurrencyCode','USD'); FOR j IN getlnrec (i.document_id) LOOP /* this way is not working */ l_children.append(json_object_t(key 'LineNumber' value j.line_id)); /* and this way is also not working */ l_children.append(json_object_t('{"ItemNumber":||"||j.item_code||"}')); l_children.append(json_object_t('{"Description":||"||j.item_description||"}')); l_children.append(json_object_t('{"Quantity":||"||j.Quantity||"}')); l_children.append(json_object_t('{"UnitSellingPrice":||"||j.line_value||"}')); * END LOOP; l_json.put('receivablesInvoiceLines',l_children); END LOOP; envelope := l_json.to_clob;