Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
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
-
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.
-
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
-
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
-
If you Google for them, you will find them in the usual places by the usual people.