3 Replies Latest reply on Jan 14, 2019 7:49 AM by Carsten Czarski-Oracle

    Create a WEB SOURCE MODULE

    CJ Bell

      Please be gentle I have tried to learn using the forum and various docs but I just don't get it :-(

       

      I am using an analytics provider called MATOMO and the have an API to access the data

       

      examples are here

       

      https://developer.matomo.org/api-reference/reporting-api#SitesManager

       

      If this is the GET request how do I make this run on Oracle APEX ??

       

      https://xxxxxx.innocraft.cloud/?module=API&method=SitesManager.getSiteUrlsFromId&idSite=7&format=JSON&token_auth=xxxxxxxxxxx

       

      running this in the browser brings me back one record in the JSON format  but other types of request can bring back many records

       

      Am I correct in thinking I can make these into a table structure that can then be treated as normal oracle table ??

       

      I have kind of created a web source module on apex.oracle.com but I'm stuck on how to use it OR if its correct

       

      many thanks

       

      Chris

        • 1. Re: Create a WEB SOURCE MODULE
          Carsten Czarski-Oracle

          Hi Chris,

           

          hmmm .. happy to help, but I would need a few more details ...

           

          I can review your web source module on apex.oracle.com, you might send login credentials privately to me (the Oracle email scheme is simple: {firstname}.{lastname}@oracle.com.

           

          Best regards

           

          -Carsten

          • 2. Re: Create a WEB SOURCE MODULE
            CJ Bell

            Hi Carsten

             

            Have emailed you, many thanks for the offer of help

             

            Chris

            • 3. Re: Create a WEB SOURCE MODULE
              Carsten Czarski-Oracle

              Hi Chris,

               

              OK - there are multiple things to consider here:

               

              1. The first thing I always do when working with REST APIs is to check whether the database can communicate well with the external service. For such a basic test, I simply go to SQL Workshop (or SQL Plus or SQL Developer) and invoke the URL using APEX_WEB_SERVICE.MAKE_REST_REQUEST:
                select apex_web_service.make_rest_request('https://adsertor.innocraft.cloud/index.php?{parameters go here}', 'GET') from dual

                Bildschirmfoto 2019-01-14 um 08.40.27.png

                On apex.oracle.com, this already leads to ORA_29273 - Certificate Validation Error. In order to access this site, some root certificates have to be installed into the database; on apex.oracle.com this is not the case. So we cannot continue testing on apex.oracle.com - however, on your own rig you can continue working:
                If you get the "Certificate Validation Error" as well, follow this blog posting in order to install the root certificate into your database:
                https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html

                Note: When working on 12.2, you have to set the Web Source Module HTTPS Host parameter (or p_https_host argument of APEX_WEB_SERVICE.MAKE_REST_REQUEST) to "innocraft-cloud-jupiter.alwaysdata.net". The result of the query should look as follows:
                Bildschirmfoto 2019-01-14 um 08.39.14.png
              2. Once you have the basic communication working, you might look into the Web Source Module again. On your apex.oracle.com example (which is exported and imported into my own instance), I had a look into the "Data Profile" section, which looked as follows:
                Bildschirmfoto 2019-01-14 um 08.12.57.png
                ... which is natural, since we had the above Certificate Validation Error, so apex.oracle.com was not able to communicate with the REST service, so a Data Profile could not be generated. The Data Profile contains meta data about the JSON response structure - this meta data allows APEX to interpret the JSON response like a table and to make the REST service available to APEX components. Since the HTTP communication now works, you can simply ...

                Pick the right "row selector". This attribute tells APEX where to find the "array of rows", which is to be exposed to APEX components, within the JSON. I reviewed the JSON response and found that there is one attribute lastVisits, which could be used. So type in lastVisits as the Row Selector, save the changes, come back and click the Rediscover Data Profile button.
                Bildschirmfoto 2019-01-14 um 08.45.04.png
                The Rediscover Data Profile button should open a dialog which shows something like the following:
                Bildschirmfoto 2019-01-14 um 08.46.43.png
                You  might then click "Extend Data Profile" and the Web Source Module Definition is complete.

              3. One Web Source Module can only contain one Data Profile, so if your JSON e.g. returns multiple arrays with different information, you need to create another web source module with the same URL and parameter settings and just different Data Profile settings ...
              4. Then create a new page or navigate to an existing page in page designer and add components to the page using this web source module.

               

              I hope this helps

               

              -Carsten