PL/SQL (MOSC)

MOSC Banner

JSON Parsing using PLSQL - Oracle Database 19c PLSQL Development

in PL/SQL (MOSC) 1 commentAnswered

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;
Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center