REST data sources with Oracle APEX: setting an external REST data source that needs an authentication token and persist the incoming data on a local table

There are a lot of tutorials and examples out there about how to set up a REST external data source on an Oracle database,  most of them are focused on the core "how-to" connect the database to an external service, avoiding any unnecessary complexity in explaining the core concepts. But the real world is not that easy, and when it comes to implementing the REST data source you really need, it might not be as simple as the overused USGS earthquake API service. 

There's a reason why the USGS earthquake API is used to teach how to use REST API calls with any platform, from Android native apps to Oracle, and it's because it is very handy and therefore very useful to learn the core concepts.

But what if your REST API needs an authentication token to be used?

After reading (and watching) some tutorials, I applied them to my needs: that is to set up a RapidAPI service as an external data source in an Oracle APEX application. In my case, it was the COVID-19 API which is a free service, but, anyway, you need to use a personal RapidAPI token to use it. I had previously subscribed to the service because I needed it for a mobile Flutter application. After that, I decided to integrate it into an OCI ADB instance as an external data source. Using APEX makes the whole thing quite straightforward, but, as always, the devil lies in the detail. What I mean to say, is that when you need an authentication key to call a REST API and the key has to be passed directly in the URL, the whole matter is relatively trivial: you'll end up with a URL request like this https://restservice.api.com/V1.0/getdata?key=[your token]&op=something&id=12345. Easy like riding a bike. But, what if you need to set the key into the request header instead?

So, in the next steps we'll:

• create a new APEX app on our ADB
• set up the COVID-19 API as an external REST data source
• call the REST service using an authorization key released by RapidAPI
• Bonus: pump the REST data into a table using a scheduled task that runs on a regular basis, to keep the local data table updated with the latest outcome from the REST service.

Creating the APEX application

Assuming that you have already set up an APEX instance on your free ADB, head to the management page of your ATP/ADW, open the APEX instance then launch APEX and sign in:

On the main page of APEX, click on "Create" then "New Application" and leave all options to their default values. Choose a name for your app (if you like you can change the theme and the usual rocket icon), then click on "Create application"

Analyze the API call with Postman

Before diving into the configuration of a REST data source in APEX, let's look closely at how this particular REST service works, with the help of our old friend Postman as a laboratory (or surgery room, if you prefer). 

We are going to use the API service to get the covid-19 statistics for Germany. The country is an optional parameter since the service can return worldwide information, but we'll use it just to add some salt to the recipe.

Our target REST API URL is https://covid-193.p.rapidapi.com/statistics

and we need to set two parameters:

• our user key
• the country for which return the data (optional)

From the RapidAPI website, on the page dedicated to the COVID-19 API, we can generate some snippets in various languages as examples of its use. If we look at our API sample call using javascript we can see something like this:

now we know that Germany will be an URL parameter, and the user key will be a property in the

request header instead. Now let's try it out using Postman:

in a new tab, select GET as the HTTP command, in the "Params" tab add a param

• KEY=country
• VALUE=Germany

now go to the "Headers" tab and add to the values these ones:

• KEY=X-RapidAPI-Key
• VALUE=[your own token]

notice that it's not required to set a header value for X-RapidAPI-Host

like in the javascript snippet, since this is accomplished by the GET request itself.

now click on the "Send" button and you'll get the response body with the information in JSON format:

Back to OCI ADB: Setting the REST data source in APEX

Now it's time to get things working into APEX. In the application, go to the "Shared components" module and in the "Data sources" pane (down on the left), and select "REST data sources"

click on the "Create" button and start the wizard.

APEX provides a cosy wizard to connect to an external REST service. In this example, we'll connect and authenticate to the COVID-19 API service with the same parameters we have seen above in Postman.

 Leave the option "Create REST Data Source from scratch" and then click on "Next"

set the "REST Data Source Type" to Simple HTTP, give a name to the service and as the URL endpoint set the one used for gathering the statistic information: https://covid-193.p.rapidapi.com/statistics.

Leave  "HTTPS Host Name" empty. Click on Next.

On the following page, you can see that the wizard has extracted the server name, the base URL and the service path from the previous information. Click Next, leave the "Pagination option" as suggested, then Next again.

On the next page, we set up the authentication parameters. Set "Authentication Required" to ON, and, as authentication type, select HTTP Header from the list. This shows up two more options, and, guess what? they are the name of the header parameter and its value, in other words:

• Name=X-RapidAPI-Key
• Value=[your own personal key from RapidAPI]

same as seen before in Postman. Now click on "Discovery" and let the magic happens

the next page shows a sample result of the REST API call. Here you can see the data returned by the call in the "Data" tab, as well as the metadata that APEX automatically built from the JSON response, in the "Data Profile" tab.

We are still missing the "Germany" parameter, it'll come soon. Now click on "Create REST Data Source".

At this point, the REST data sources management page shows up and we can see the data source just created. Click on the "REST Source Name" to open and edit its properties.

Scroll down the page to the "Operation" sections, where we can see the only defined operation, which is a GET verb. On the right there's a button to test the REST call, let's try it.

As a result, we get all the COVID statistics returned by the call to the API endpoint and, as we can see, they are about all the countries covered by the service worldwide.

Now let's try to define the parameter to gather results only for Germany. Click on the "Go Back" button, choose the "Parameters" tab, and then click on "Add Parameter". 

In the pop-up window:

• choose "Query String variable" from the Type drop-down list. 
• set name = country
• set value = Germany
• set direction=in

then click on "Apply changes"

Now if we run the GET operation with "Test Operation" the window "Test Results" will show only the results for Germany.

Loading and saving the REST data in a database table

APEX has a nice feature to persist and update in a local table the data coming from a REST resource, to make them available to other applications and operations as common database objects, such as tables, views and so forth. 

First, we need to set up where to store the data from the REST source.

From the "Rest Data Source" management window go to "Manage Synchronization" on the right

In the next window, we configure the options to store the data coming from the REST data source.

• Local table owner = [select your schema]
• Synchronise to = New Table
• Table name = [choose a name for the table, e.g. REST_DATA_LOCAL]

Click on "Save"

The following window tells us that the table does not exist yet, and displays two buttons:

• One button to show the DDL statement auto-generated to create the table where every column is mapping an attribute of the JSON object returned from the REST call. You can change the table definition and save the script.
• One button to run the DDL script and create the new table.

Click on "Create table"

Now the window shows that our table is ready for synchronisation. We now have to set some more options to put the synchronization at work.

• Synchronization type: since we are retrieving statistic data for the latest available day, we can choose "Append" to keep older data or "Replace" if we want to keep only the latest ones. 
• Synchronization schedule: how often do we want to update the data? Click on the "Schedule Builder" to pop up a modal window and set the schedule options:

Here we can choose:

• Frequency: between weekly, daily, hourly, minutely 
• Interval: sets how often the recurrence repeats. For example, if we choose a daily frequency with an interval of 1, that means every day, with an interval of 2, that means every 2 days and so on.
• Execution Hour: the hour at which the synchronization runs, between 0 and 23 (no AM/PM required😀)
• Execution Minute: well, pretty clear

Note that this feature is using the Database Scheduler Calendaring Syntax.
If you try, you'll see that changing the Frequency option changes the options available, e.g. choosing Weekly shows up the days of the week.

Since in this API service the data returned are updated daily, and as you can see the JSON object holds a timestamp, we can set:

• Daily as the value for Frequency
• 1 as the value for Interval
• specify a time following the one reported into the JSON object, to make sure we have the latest information available.

then we can click on "Set Execution Interval" and save the schedule.

The back window is updated with the schedule timing configuration and the job is activated

now we can click on "Save and Run" and see what happens.

You can see that "Job status" starts spinning and after a few seconds it shows (hopefully) the message "Success" then some pieces of information about the executed job and the next one scheduled.
Now a button saying "View in SQL Workshop" is visible on the right of the page.

Click on it.
This pops up the database object browser showing all the informations about the table we have just created to store the data returned from the REST service.
In the "Data" tab you can see the data has been retrieved from the REST service call and saved into the table.

and that's it! We have configured an external REST data source and set up a scheduled task to persist its data on a local database table and update them on a regular basis.

Have fun!