Reading data in an inner array with pljson

Question

Sample JSON:

{"TestResults": [{
    "Messages": ["PASS: Status date 7/1/2002, ID"],
    "Status": "Pass",
    "Name": "IFTA License"
},
{
    "Messages": ["Carrier compliant"],
    "Status": "Pass",
    "Name": "MCOLS carrier compliance check"
},
{
    "Messages": ["No"],
    "Status": "Pass",
    "Name": "IFTA Revoked"
},
{
    "Messages": ["PASS=> Not under Federal Out Of Service order"],
    "Status": "Pass",
    "Name": "Federal Out of Service Status"
},
{
    "Messages": ["PASS => Carrier status code 100: Active ID"],
    "Status": "Pass",
    "Name": "IRP Status"
},
{
    "Messages": ["PASS => Status Code 1: Active"],
    "Status": "Pass",
    "Name": "IFTA Status"
},
{
    "Messages": ["PASS => UCR fee paid for 2017",
    "PASS => UCR fee paid for 2016"],
    "Status": "Pass",
    "Name": "UCR Fee Status"
},
{
    "Messages": ["PASS => MCMIS Status = A: Active"],
    "Status": "Pass",
    "Name": "USDOT Status"
},
{
    "Messages": ["PASS=> Not under Federal Out Of Service order"],
    "Status": "Pass",
    "Name": "PRISM Target Status"
},
{
    "Messages": ["PASS => ISS Score: 42 No Inspection Warranted"],
    "Status": "Pass",
    "Name": "ISS2 Score"
},
{
    "Messages": ["PASS => MCS-150 Up to Date: Last Update 4/28/2016"],
    "Status": "Pass",
    "Name": "MCS-150 Status"
},
{
    "Messages": ["PASS => Carrier is authorized"],
    "Status": "Pass",
    "Name": "Operating Authority Status"
}]

}

Code utilizing pljson:

DECLARE

     l_http_req UTL_HTTP.REQ;
     l_http_resp UTL_HTTP.RESP;
     l_response CLOB;
     l_buffer VARCHAR2(4000);
     l_response_json common.json;
     l_testresults_list common.json_list;
     l_messages_list common.json_list;

BEGIN

    utl_http.set_wallet('file:/home/oracle/wallet/blah',NULL);
    l_http_req := utl_http.begin_request('https://some_url', 'GET');
    utl_http.set_header(l_http_req, 'Content-Type', 'application/json');
    utl_http.set_authentication(l_http_req, 'user', 'password'); 

BEGIN
dbms_lob.createtemporary(l_response, TRUE);
dbms_lob.open(l_response, dbms_lob.lob_readwrite);
l_http_resp := utl_http.get_response(l_http_req);
LOOP
    utl_http.read_text(l_http_resp, l_buffer, 2000);
    dbms_lob.writeappend(l_response, LENGTH(l_buffer), l_buffer);
END LOOP;
EXCEPTION
    WHEN utl_http.end_of_body THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
    END;
    dbms_lob.close(l_response);
    utl_http.end_response(l_http_resp);

-- Convert CLOB content to JSON object.
BEGIN
    l_response_json := common.json(l_response);
    EXCEPTION
        WHEN OTHERS THEN
        RAISE;
    END;

    BEGIN
        l_testresults_list := json_ext.get_json_list(l_response_json, 'TestResults');
        FOR i IN 1..l_testresults_list.COUNT LOOP
            dbms_output.put_line('name: '||common.json_ext.get_string(common.json(l_testresults_list.GET(i)), 'Name'));
            dbms_output.put_line('status: '||common.json_ext.get_string(common.json(l_testresults_list.GET(i)), 'Status'));
            l_messages_list := json_ext.get_json_list(common.json(l_testresults_list.GET(i)), 'Messages');
            FOR j IN 1..l_messages_list.COUNT LOOP
                dbms_output.put_line('messages: '||common.json_ext.get_string(common.json(l_messages_list.GET(j)), 'Messages'));
                dbms_output.put_line('count : '||j);
            END LOOP;
        END LOOP;
    END;

    EXCEPTION
        WHEN OTHERS THEN
            utl_http.end_response(l_http_resp);
        RAISE;      
END;

I'm able to extract the name and status values from this JSON, but when this line of code is executed to extract the messages:

dbms_output.put_line('messages: '||common.json_ext.get_string(common.json(l_messages_list.GET(j)), 'Messages'));

I receive an ORA-30625: method dispatch on NULL SELF argument is disallowed

Has anyone been able to successfully read values from an inner array with pljson?

Answer 1

Since you are working with the array, you should be able to simply:

dbms_output.put_line( l_messages_list.get(j).to_char() );