2 Replies Latest reply on Jun 19, 2019 3:59 PM by Olafur T

    Importing JSON data from web-service

    3125437

      Hello,

       

      We are trying to find a way to import JSON data from a web-service instead of from a file.

       

      I think the information in the Doc ID 1923422.1 will help with the JSON formatting and syntax parts, but what I am specifically looking for is a way to import data directly from a web-service.

       

      Any direction would be greatly appreciated!

       

      Sincerely,

      Anthony

        • 1. Re: Importing JSON data from web-service
          Peter de Vaal

          You can use ORDS to define a REST module to import the JSON. The JSON should be passed to the webservice as payload, and can be obtained in the REST module's POST method as either the :body (for binary) or :body_text (for textual encoded) implicit parameter. You can then store the JSON in a BLOB (from :body) or CLOB or VARCHAR2 (from :body_text) column. Then you can do anything with it: parse the JSON (e.g. use package APEX_JSON) and then store the result in other columns, or keep everything in that column and query the column directly using SQL/JSON (Oracle 12.2 or higher).

          • 2. Re: Importing JSON data from web-service
            Olafur T

            Hi,

             

            Simplest way is to consume them as as view  or call the webservice in PL/SQL using f.i. apex_web_service and then manipulate the json/xml data into tables..

             

            I made this simple example using an open web service supplying a sample json reply.

            with j as
            (select httpuritype('http://jsonplaceholder.typicode.com/todos/').getClob() as json from dual)
            select jdata.*
              from j,
                   json_table(j.json,
                              '$[*]'
                              columns(userid number path '$.userId', id number path '$.id', title varchar2(200 char) path '$.title', completed varchar2(10 char) path '$.completed')) jdata
            

             

             

            The results end up as a normal table so I could save the data either with a "create table as" or as I normally do with these kind of services, I create a view and a materialized view on top of that. (I have many materialized views like this getting xml/json data from raw github projects)

             

            create view v_tododata as 
             with j as
              (select httpuritype('http://jsonplaceholder.typicode.com/todos/').getClob() as json from dual)
             select jdata.*
               from j,
                   json_table(j.json,
                              '$[*]'
                              columns(userid number path '$.userId', id number path '$.id', title varchar2(200 char) path '$.title', completed varchar2(10 char) path '$.completed')) jdata;
            
            create materialized view mv_tododata refresh on demand as 
             select * From v_tododata;
            

             

            I find this the easiest way to maintain dependence on external data via web services or external files with http/https.

             

            Regards

            Oli

            1 person found this helpful