Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Moustapha.

Asked: March 13, 2025 - 8:53 am UTC

Last updated: March 13, 2025 - 2:03 pm UTC

Version: 12 C

Viewed 1000+ times

You Asked

now i have json response from oracle service like following code

{
"Status": null,
"RequestNumber": null,

"validationResults":
[
{
"code":E1000,
"details":'Validation Error'

}
]

}

now i can read keys Status and request number like below

        obj_rsp       :=json.json(content_resp);
       

        v_status      :=json.json_ext.get_string(obj_rsp, 'status');
        v_req_no     :=json.json_ext.get_number(obj_rsp, 'data.requestNumber');
        --
        /*v_resp_code  :=json.json_ext.get_string(obj_rsp, 'validationResults.Code');
        v_resp_desc  :=json.json_ext.get_string(obj_rsp, 'validationResults.details');*/


i need to get <validation resultes>
Code and details

how could i do it ?

and Chris said...

The values are inside an array. So you need to get the first element of the array, then the values from that.

There are various ways you can do this. e.g. this uses JSON_table rather than PL/SQL objects:

select * from json_table (
  q'!{ 
    "Status": null,
    "RequestNumber": null,
    "validationResults": [ 
      { "code":"E1000", "details":"Validation Error" }
    ]
  }!', '$.validationResults[*]'
  columns (
    code, details
  )
);

CODE     DETAILS             
E1000    Validation Error   


Note that the example JSON is invalid: the values need to be in double quotes. I'm guessing this is a copy-paste error. If that's the data you're getting then you'll need to fix this before parsing the JSON.

Rating

  (1 rating)

Comments

Great

Moustapha Muhmmad, March 18, 2025 - 10:04 am UTC

Thanks 🙏 it's working

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database