ORDS, SODA & JSON in the Database

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.

in the above code, the l_children.append section is causing the error.

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

         -- l\_children.append(json\_object\_t(key 'LineNumber' value j.line\_id));  
          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;

here is the error

Error report - ORA-40441: JSON syntax error ORA-06512: at "SYS.JDOM_T", line 4 ORA-06512: at "SYS.JSON_OBJECT_T", line 28 ORA-06512: at "INT_STG_DATA.XXRR_BILLING_INVOICE_PROC", line 113 ORA-06512: at "INT_STG_DATA.XXRR_BILLING_INVOICE_PROC", line 113 ORA-06512: at line 4 40441. 00000 - "JSON syntax error" *Cause: The provided JavaScript Object Notation (JSON) data had invalid syntax and could not be parsed. *Action: Provide JSON data with the correct syntax.

This sounds simple enough, any help will be appreciated.

Darsh

Darsh