Hi,

I am looking for options and best practices for the following use case:

- consume an external API every 15 seconds, which returns JSON data and insert the response into an oracle db table
- the JSON response has around 80 fields which need to be mapped to oracle datatypes with a mix of boolean, string and number fields
- I have created a simple utl_http request which returns the JSON in string format but I am stuck on how to best proceed for mapping the returned data string
- the procedure needs to also remember the last insert id to add as a parameter in the api url so that only new records are returned

Questions:

1. Is it best to run a PL/SQL Procedure with Scheduler or would API manager be better suited?
2. Is there a size limit to the JSON response?
3. Are there plugins required to map JSON to oracle datatypes. If so are there options to add these plugins to AWS RDS hosted oracle db?

Any help you can provide would be greatly appreciated.

create table t( doc_id  int, 
 doc_details varchar2(1000)
);

insert into t values (1 , '{"id":1, "name" : "Jeff"}' );
insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );
insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );
insert into t values (4 , '{"name" : "John", "status":"Silver"}' );
commit; 

select json_value(doc_details, '$.name') from t;

JSON_VALUE(DOC_DETAILS,'$.NAME')  
Jeff                              
Jane                              
Jill                              
John