Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

Call Rest API and store data into Oracle Schema

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.

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,056 Employee

    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_HNRAA
  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy

    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
  • User_HNRAA
    User_HNRAA Member Posts: 2 Green Ribbon

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,056 Employee

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