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;