Objective: Parse JSON content from APEX REST Data Source that includes arrays.
Problem: encountering Expression is of wrong type (noted as comment in code) and Get_String must be declared (noted as comment in code). Code and sample JSON content below. Tried a variety of things and feel like I am missing something fundamental…
Example Content: Content would also have multiple Client entries.
{
"nextPageUrl": null,
"previousPageUrl": null,
"results": [
{
"id": 198334,
"clientCode": "ABCCOL",
"clientName": "ABC Collision",
"securityServiceGroup": {
"id": 2,
"name": "Demo"
},
"links": [
{
"href": "https://www.extrest.com/rest/api/clients/198334",
"rel": "Individual"
},
{
"href": "https://www.extrest.com/rest/api/clients/198334?Details=True",
"rel": "Detailed"
},
{
"href": "https://www.extrest.com/rest/api/clients/198334/subdivisions",
"rel": "SubDivisions"
}
]
}
],
"totalItems": 1,
"totalPages": 1.0
}
Code:
DECLARE
l_params apex_exec.t_parameters;
v_Response Varchar2(4000);
v_Results JSON_OBJECT_T;
v_Clients JSON_OBJECT_T;
BEGIN
apex_session.create_session(
p_app_id => 100,
p_page_id => 1,
p_username => 'MYID'
);
apex_exec.execute_rest_source(
p_static_id => 'clients_complete_list',
p_operation => 'GET',
p_parameters => l_params );
v_RESPONSE := apex_exec.get_parameter_clob(l_params,'RESPONSE');
DBMS_Output.Put_line('Result: ' || v_Response);
v_results := JSON_OBJECT_T.PARSE(v_Response);
For i In 1..v_Results.get_Size() Loop
v_Clients := v_results.get('results[i]'); -- EXPRESSION IS OF WRONG TYPE ERROR --
DBMS_Output.Put_Line('ID: ' || v_Clients.get_number('id'));
DBMS_Output.Put_line('ClientCode: ' || v_Clients.get_string('clientCode'));
DBMS_Output.Put_Line('ClientName: ' || v_Clients.get_String('clientName'));
DBMS_Output.Put_line('ServiceGroup: ' || v_Clients.get_number('securityServiceGroup'));
For j in 1..v_Clients.get_array('links').get_Size() Loop
DBMS_Output.Put_Line('id: ' || v_Clients.get_number('id'));
DBMS_Output.Put_line('links: ' || v_clients.get_array('links').get(j).get_string('href')); -- GET STRING MUST BE DECLARED --
End Loop;
End Loop;
END;