I'm new to RESTfull API and JSON stuf. I have a need to call some RESTfull APIs from PL-SQL to capure the JSON objects returend by the API. Now I just want to extract requred data from JSON object and then pass those data to respective Oracle API to save them in the database.
This is how I call REST APIs using UTL_HTTP package from PL-SQL code ...
req := UTL_HTTP.BEGIN_REQUEST('https://xxxx/api/job', 'GET', 'HTTP/1.1');
UTL_HTTP.set_header(req, 'Content-Type', 'application/json');
UTL_HTTP.set_header(req, 'apikey','xxxxx');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
This is a sample JSON object received...
{
"pageNumber": 0,
"pageSize": 10,
"totalElements": 3,
"totalPages": 1,
"jobs": [
{
"companyId": 001,
"jobId": 1101,
"title": "Software Developer",
"jobStatus": "Open",
"description": "Seeks Software Developer",
},
{
"companyId": 002,
"jobId": 1102,
"title": "Software Architect",
"jobStatus": "Open",
"description": "Seeks Software Architect",
}
]
}
Now I just trying to call an Oracle API from PL-SQL code for the data persistance in the database...
APPS.VIRT_INSERT_JOB(
companyId,
jobId,
title,
jobStatus,
description
);
What would be the esiest way to get the values in the JSON objects to pass it into this API?
Is there a way to automate this like in GWT we convert JSON to java script objects using JsonUtils.safeEval()?