Call Rest API and store data into Oracle Schema

User_HNRAA

Hello,

Can I get some ideas how to accomplish using ORDS and Rest API

We have a schema with set of tables used by a web application , some part of the application and corresponding database tables are moved to cloud based application. Original application is still functional with limited web functionality and its related tables its original schema.

now my problem is I have to access Rest API from new cloud based application and use that data in my original application to make it functional. Most the database related transactions are written in PL/SQL in packages. Now I have to change the PL/SQL logic calling the Rest API .

What are the options for me, I am thinking of following

Call Rest API from oracle and convert the json content into oracle table data fields and store in the database - is it possible solution? if so , how?

or Change the PL/SQL code (which has joins with multiple tables and complex queries) change it in such a way to call Rest API and join with some existing tables in original schema - which I am not sure if possible.

thatJeffSmith-Oracle

If you have a rest api with ORDS, it can accept a json payload and it can be stored natively as a json document in the database, or as rows in a table. There are multiple code/feature paths you can take to accomplish this.

ORDS is responsible for serving up the REST APIs, not for CALLING APIs. To call a REST API from the database, you'd use an apex helper package or write your own UTL.HTP calls.

User_H3J7U

Call Rest API from oracle and convert the json content into oracle table data fields and store in the database - is it possible solution? if so , how?

-- Original rest http://openexchangerates.org/api/currencies.json:
-- {
--  "AED": "United Arab Emirates Dirham",
--  "AFN": "Afghan Afghani",
--  "ALL": "Albanian Lek",
--  "AMD": "Armenian Dram",
--  "ANG": "Netherlands Antillean Guilder",
-- ...
with function json_transpose(jsn clob) return clob as
-- {key1:value1,...} -> [{name:key1,value:value1},...]
  ctx dbms_mle.context_handle_t;
  res clob := empty_clob()||'';
begin
  ctx := dbms_mle.create_context();
  dbms_mle.export_to_mle(ctx, 'jsn', jsn);
  dbms_mle.eval(ctx, 'JAVASCRIPT', q'<
    const bind = require("mle-js-bindings");
    const lob = bind.importValue("jsn");
    const src = JSON.parse(lob.read(lob.length(),1));
    const tgt = [];
    for(const elm in src) tgt.push({name:elm,value:src[elm]});
    tgt
    >', res);
  dbms_mle.drop_context(ctx);
  return res;
end;
rest as (select json_transpose(httpuritype('http://openexchangerates.org/api/currencies.json').getclob()) j from dual)
select *
from rest
     nested j[*] columns(name, value)
where rownum<=5;
/

NAME  VALUE                                   
----- ----------------------------------------
AED   United Arab Emirates Dirham             
AFN   Afghan Afghani                          
ALL   Albanian Lek                            
AMD   Armenian Dram                           
ANG   Netherlands Antillean Guilder           

User_HNRAA

https://community.oracle.com/tech/developers/discussion/comment/16816827#Comment_16816827

Thanks Jeff, Yes I need to call the Rest API by GET , can you send me links for example usiing Apex package and utl htttp

thatJeffSmith-Oracle

If you Google for them, you will find them in the usual places by the usual people.