APEX Web Source Module: step by stepPublished on: Author: Peter de Boer Category: Oracle
In this article, I will show how I created a Web Source Module in Oracle Application Express (APEX) that requires API-subscription-key authentication and a close inspection of the JSON-output of the Web Service.
If you are new to Web Source Modules or if you want to have a really detailed, step-by-step blog on how to set up a Web Source Module, have a look at: https://ujnak.github.io/APEX/creating_web_source_module/creating_web_source_module.html. Of course, you need an operational APEX Workspace if you want to try for yourself. If you don’t have one, you can get it on apex.oracle.com. Or use an Always Free cloud service.
Web Source Modules can be useful when you want to use data that are not located on the database server where APEX runs on. You can both read and write data using Web Source Modules.
In this example, I will create a Web Source Module that retrieves train station information from the Dutch Railway company (Nederlandse Spoorwegen). This company provides access to some of their data via an API portal. This API portal requires you to create an account (free) and subscribe to an API. Once subscribed, I can look up the Request URL of the API and authentication key.
Step 1: Create a Web Source Module
Let’s get started: create a new Web Source Module in the Shared Components in your application (or by pressing CTRL-quote and type “Web Source” and press enter).
Press the Create button. In the Modal Dialog, leave the radio group on “From scratch” and press Next.
I will create a “Simple HTTP” Web Source Module.
The URL Endpoint is basically the location from where the APIs are accessible. Press Next.
The Service URL Path specifies which API (and which version) we want to use from the endpoint. In this example, I’m using the places-api version 2.
Now, we have to configure the authentication that is needed to access the API.
Although authentication is required for the places-api, I’m going to finish the wizard by pressing the “Create Module Manually” button. The default authentication types in the wizard do not supply a request header configuration, and I got stuck a couple times using the “Advanced” button. Open the Web Source Module:
Next, press the “Add Parameter” button in the Module Parameters region.
I found the name of the HTTP Header parameter in the API description in the NS API Portal. The subscription key can be copied from the Profile page.
Step 2: Configure the Web Source Operation
The GET operation is already available, but it needs some adjustments. Edit the GET operation in the Operations region.
The URL pattern needs to be supplied to request the proper operation as described in the API documentation on the NS API Portal.
In this operation, I use 2 parameters: type and countries. I need these parameters, because I am now only interested in Dutch train stations. These parameters need to be added to the Operation Parameters:
For parameter “countries”, the value “NL” can be entered (with the quotes).
Step 3: Configure Data Profile
The last thing you need to do in the Web Source Module is to configure the Data Profile. This will map the JSON structure to column definition.
Press the “Edit Data Profile” button.
To find out what the row selector should be, you first have to inspect the JSON-result of the API-call. The JSON output shows a nested structure where we see a collection of “locations” within the value of “payload”.
This results in the Row Selector payload.locations
When the row selector is supplied, the available columns can be discovered by APEX by making an API-call. Press the Rediscover Data Profile. It will show a list of available JSON-field and makes a proposition for a set of new columns.
Press the “Replace Data Profile” to remove the existing (sample) columns.
Finally, press the Apply Changes button. With that, we are finished with the Web Source Module.
Step 4: Check your results
Now it’s time to test the Web Source Module. On a (new) page, create an Interactive Report. Choose Web Source as Location and pick the newly created Web Source Module.
Press “Save” and then run the page:
You can modify the layout of this Interactive Report (IR) just like any other IR. It’s also possible to make a Classic Report on the Web Source Module. It isn’t possible (yet) to make an Interactive Grid using the Web Source Module in this declarative way. However, if you are willing to tweak it a little, you should take a look at this blog: https://blogs.oracle.com/apex/web-source-modules-and-the-interactive-grid-part-1
Note: in APEX 19.2, the use of a Web Source Module as a data source for the Interactive Grid is possible.
Debugging the Web Source Module
Unfortunately, there is no debugging option in the Web Source Module configuration page. If for whatever reason the configuration does not work as expected (especially the discovery of the data profile), you can create the Interactive Report (IR) with the (incomplete) Web Source Module and run the IR with debug on. This way, you can find relevant information about how the API call is made and what possibly the problem is.