Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help parsing JSON string with array, oracle 19c, APEX 23.2.3

Joe KerrApr 20 2024

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;
This post has been answered by Solomon Yakobson on Apr 20 2024
Jump to Answer
Comments
Post Details
Added on Apr 20 2024
2 comments
2,268 views