Handling JSON objects in SQL

See more:

Java

SQL

Oracle

JSON

REST

GWT

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()?