I have a following code in a PLSQL Proc.
Declare
url VARCHAR2(1000) := p_url;
l_json json_object_t := json_object_t();
l_children json_array_t := json_array_t();
envelope CLOB;
response_text CLOB;
CURSOR getHdrRec IS
SELECT hdr.bill_to_account_id,
hdr.bill_to_account_name,
hdr.bill_to_party_number,
hdr.bill_to_party_site_number,
hdr.vat_code,
hdr.vat_code_description,
hdr.vat_code_value,
hdr.due_date,
hdr.due_date2,
hdr.due_date3,
hdr.company_id,
hdr.depot_id,
hdr.document_id,
hdr.trx_date,
hdr.project_id,
hdr.ship_to_account_id,
hdr.ship_to_party_site_number,
sysdate creation_date
FROM xxrr_billing_invoice_hdr_stg hdr
WHERE hdr.record_status = 'N';
CURSOR getLnRec (c_document_id in VARCHAR2) IS
SELECT ln.line_id,
ln.document_id,
ln.item_code,
ln.item_definition_code,
ln.item_description,
ln.quantity,
ln.hire_period3,
ln.line_value
FROM xxrr_billing_invoice_line_stg ln
WHERE ln.document_id = c_document_id
AND ln.record_status = 'N';
BEGIN
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('{"LineNumber":"'|| 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;
envelope := l_json.to_clob;
dbms_output.put_line('clob:'||envelope);
/* Select request_fusion(
p_path => '/',
g_fusion_url => p_url,
p_method => 'POST',
g_fusion_cred => 'FSCM_API',
p_body => envelope
) into P_response from Dual;*/
dbms_output.put_line('P_RESPONSE clob:'||p_response);
END;
This code generates following JSON.
"{
"BillToCustomerNumber" : "29292",
"BillToCustomerName" : "ABCD Corp",
"BillToSite" : "136036",
"DueDate" : "2023-07-31T00:00:00",
"BusinessUnit" : "XYZ Corp",
"CrossReference" : 56565656,
"TransactionDate" : null,
"CreationDate" : "2023-08-22T20:22:43",
"ShipToCustomerNumber" : "29292",
"ShipToSite" : "136037",
"TransactionSource" : "Equipment Rental",
"TransactionType" : "Invoice",
"InvoiceCurrencyCode" : "USD",
"receivablesInvoiceLines" :
[
{
"LineNumber" : "1"
},
{
"ItemNumber" : "103629"
},
{
"Description" : "TEST DESCRIPTION1"
}
]
}"
The the way receivablesInvoiceLines children are generated in above JSON is creating errors. My expectation is that following is how the JSON should generate.
{
"BillToCustomerNumber" : "29292",
"BillToCustomerName" : "ABCD Corp",
"BillToSite" : "59329",
"DueDate" : "2023-07-31T00:00:00",
"BusinessUnit" : "XYZ Corp",
"CrossReference" : "56565656",
"TransactionDate" : "2023-07-07",
"ShipToSite" : "136037",
"TransactionSource" : "Equipment Rental",
"TransactionType" : "Invoice",
"InvoiceCurrencyCode" : "USD",
"receivablesInvoiceLines" :
[
{
"LineNumber" : "1",
"ItemNumber" : "103629",
"Description" : "TEST DESCRIPTION1",
"Quantity" : 10,
"UnitSellingPrice" : 1100
}
]
}
How can I fix my code under l_children.append to generate JSON correctly?
Thank you, Darsh
Here is a similar example based on the emp/dept sample dataset. The issue you have is that you have declared l_children as data type json_array_t, but you don't want it to be an array of elements, you want it to be an array of json objects.
What you have is similar to this (just printing the first line of output):
DECLARE
l_json json_object_t := json_object_t();
l_children json_array_t := json_array_t();
CURSOR c_dept IS
SELECT dname, deptno FROM dept;
CURSOR c_emp (deptno_i IN number) IS
SELECT ename, job FROM emp WHERE deptno = deptno_i;
BEGIN
FOR i IN c_dept LOOP
-- Create JSON payload
l_json.put('dname',i.dname);
FOR j IN c_emp (i.deptno) LOOP
l_children.append(json_object_t('{"ename":"'|| j.ename || '"}'));
l_children.append(json_object_t('{"job":"'|| j.job || '"}'));
END LOOP;
l_json.put('employees',l_children);
dbms_output.put_line(l_json.to_clob);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
{
"dname": "ACCOUNTING",
"employees": [
{
"ename": "KING"
},
{
"job": "PRESIDENT"
},
{
"ename": "CLARK"
},
{
"job": "MANAGER"
},
{
"ename": "MILLER"
},
{
"job": "CLERK"
}
]
}
It is working as expected. Children is defined as an array but... you want the object inside the array. So add the elements to the object, then add the object to the array:
DECLARE
l_json json_object_t := json_object_t();
l_children json_object_t := json_object_t();
l_children_arr json_array_t := json_array_t();
CURSOR c_dept IS
SELECT dname, deptno FROM dept;
CURSOR c_emp (deptno_i IN number) IS
SELECT ename, job FROM emp WHERE deptno = deptno_i;
BEGIN
FOR i IN c_dept LOOP
-- Create JSON payload
l_json.put('dname',i.dname);
FOR j IN c_emp (i.deptno) LOOP
l_children.put('ename',j.ename);
l_children.put('job',j.job);
END LOOP;
l_children_arr.append(l_children);
l_json.put('employees',l_children_arr);
dbms_output.put_line(l_json.to_clob);
END LOOP;
END;
/
{
"dname": "ACCOUNTING",
"employees": [
{
"ename": "KING",
"job": "PRESIDENT"
},
{
"ename": "CLARK",
"job": "MANAGER"
},
{
"ename": "MILLER",
"job": "CLERK"
}
]
}