How extract data from JSON data format in Oracle?
I have a webservice which return data in JSON format.
E.g.
{
"sourceSystem": "SYS",
"messageId": "12364569",
"creationDtTm": "2022-07-24 12:34:59",
"status": "ACC",
"tranBrnCd": "50",
"tranDt": "2022-07-24",
"tranBtchNum": "30",
"acntngDtTm": "2022-03-24 12:38:59"
}
I want to extract the values of the fields sourceSystem, messageId, creationDtTm, status, tranBrnCd, tranDt, tranBtchNum and acntngDtTm and save it in a table.
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Is there any database procedure to convert the JSON data set so that we can save it in a table?
Thanks,
Dinesh