I use SQLcl a lot and install it on every environment I work on. Its fully compatible with SQL*Plus and has useful extensions to interact with OCI, Autonomous Databases and Data Pump.

My SQLcl configuration file is named 'login.sql' and located in the '~/work' directory. I also keep my 'tnsnames.ora' file here.

The location of these two Oracle configuration files is configured in '~/.bashrc'.

# Oracle TNS location
export TNS_ADMIN=$HOME/work

# SQLCL login file
export SQLPATH=$HOME/work

This is my SQLcl configuration file.

set editor emacs
set statusbar on
set statusbar add timing
set sqlformat ansiconsole
set highlighting on
set highlighting keyword foreground green
set highlighting identifier foreground magenta
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment background white
set highlighting comment foreground black

Sample output

select count(*) from dba_objects;

COUNT(*)
________
358710

emacs # 1:0 | BILLY | EDA_DEMO | 00:00:00.953

I like reading about the hardware and software various people use so here's my version.

Introduction¶

I work for Oracle as a technical consultant based in the UK (fully remote). I'm old and evolved from a Unix/C developer to PL/SQL, Oracle DBA and now work on database migrations and Oracle Cloud Infrastructure (OCI).

Hardware¶

Lenovo ThinkCentre M900¶

• Intel i7-6700
• Memory 48GB (upgraded from 16GB)
• Disk 1TB

Samsung Galaxy S24¶

Expensive phone kindly provided by my employer. I don't use many apps (Gmail, WhatsApp, BBC Sport, Tusky).

MacBook Pro¶

Expensive laptop kindly provided by my employer. I use it on the rare occasions I need to attend an office or customer site.

Huawei Android tablet¶

I don't have a personal laptop so I use this for idly wasting time.

Synology¶

Years ago, I acquired a FreeNAS unit after almost losing an essay (and my marriage) my wife had written for a Masters course. I subsequently replaced the FreeNAS with a Synology DS420+ which works fine (file backups, Jellyfin music server). The NAS has saved my bacon on more than one occasion and now I can't imagine being without it.

Software¶

Arch Linux¶

Former serial distro hopper. Now happy with Arch and Gnome desktop. Arch just works, has great documentation and provides a brilliant development environment (comprehensive range of latest packages).

GNU Emacs¶

I have used GNU Emacs for many years but am still learning about its power daily. I try to use core packages and keep my configuration minimal although I build the latest bleeding edge version (30.x).

• Theme - Used 'monokai' for ages and now adopted Prot's excellent 'modus-vivendi-tinted'.
• Orgmode
• Org Roam
• Vertico, Consult, Maginalia, Orderless
• Magit
• Jinx (spell checker)
• Dashboard
• Undo Tree
• Pass

Thunderbird¶

My employer uses Outlook but we can run any email client so I run a local Dovecot IMAP server and Thunderbird.

I use Thunderbird to automatically fetch messages from Outlook and filters in a vain attempt to try to reduce the size of INBOX.

VirtualBox¶

I use VirtualBox a lot for demo environments, testing new Oracle features and experimenting.

I've also used Docker/Podman but struggle to fully embrace it as I prefer full control (and don't really understand Docker compose files).

Corporate¶

Standard list of tedious corporate applications I have to use. Most of these work on Arch.

• Zoom
• Teams
• Slack
• Confluence
• Jira
• VS-Code/SQL Developer (Oracle database client).

Occasionally I find myself migrating Oracle databases between environments using Data Pump.

Enterprise applications tend to use multiple tablespaces for logical separation (and historical reasons). However, this isn't possible when the target database is Autonomous Database (ADB) hosted on Oracle Cloud Infrastructure (OCI).

ADB is a managed service so DBA's can't create tablespaces and are limited to 'DATA'.

However, Oracle 19c Data Pump supports wildcards for the REMAP_TABLESPACE parameter which allows multiple tablespaces from the existing application to be mapped to the ADB DATA tablespace.

#!/bin/bash

for SCHEMA in MYSCHEMA
do
  PARFILE=/tmp/dpexp_$$.par

  cat > ${PARFILE} <<EOF
schemas=${SCHEMA}
logfile=imp_schema_${SCHEMA}.log
directory=TEST_DIR
dumpfile=exp_schema_${SCHEMA}.dmp
transform=disable_archive_logging:Y
table_exists_action=REPLACE
remap_tablespace=%:DATA
metrics=Y
logtime=ALL
EOF

  cat ${PARFILE}
  impdp user/pass@DB parfile=${PARFILE}
done

More info - Oracle 19C Data Pump documentation

I was interested by Kev Quirk's Blog Questions Challenge, so here's my answers.

To recap, the questions are:

1. Why did you start blogging in the first place ?
2. What platform are you using to manage your blog and why did you choose it ?
3. Have you blogged on other platforms before ?
4. How do you write your posts? For example, in a local editing tool, or in a panel/dashboard that's part of your blog ?
5. When do you feel most inspired to write ?
6. Do you publish immediately after writing, or do you let it simmer a bit as a draft ?
7. What's your favourite post on your blog ?
8. Any future plans for your blog ? Maybe a redesign, a move to another platform, or adding a new feature ?

Why did you start blogging in the first place ?¶

I had a web site from 1999 but this was essentially a set of static HTML pages.

I'm an Oracle DBA/developer and there was an active Oracle blogging community who first piqued my interest in back in 2005. I am a serial experimenter and curious about new technologies so initially I chose Blogger and subsequently switched to Wordpress.

What platform are you using to manage your blog and why did you choose it ?¶

This blog currently uses Emacs, Hugo, GitHub and Netlify as I wanted to use as many moving parts as possible. This stack is also essentially a 'free' solution.

Have you blogged on other platforms before ?¶

Err, yes. I've used many blogging platforms (Blogger, Wordpress, Joomla, Drupal, Tumblr, Posterous, Serendipity, Jekyll, Pelican, Octopress, Habari, Nikola, Eleventy, write.as, Hugo).

How do you write your posts ?¶

Now I write in Emacs and orgmode. This was a recent, conscious decision as I wanted to standardise on orgmode markup. Trying to remember the subtle differences between Markdown and orgmode formats was irritating.

When do you feel most inspired to write ?¶

Unfortunately not very often.

Years ago, I tended to be prompted to post by other bloggers but, with the advent of the immediacy of social media, sadly the blogging community and the number of feeds in my RSS client has shrunk considerably.

Plus I'm lazy. Incredibly lazy.

Do you publish immediately or have Draft posts ?¶

Normally, I publish immediately. I really don't like content sitting in 'Drafts' which is akin to a dripping water tap, taunting me, nagging me.

However, I am slightly pedantic about presentation and typos, in particular, so will always preview the post locally in Hugo before publishing.

What's your favourite post on your blog ?¶

Easy. Two. Both involve hamsters.

• a narrow escape
• dead body under the floorboards

Any future plans for your blog ?¶

Possibly. I'm quite happy with my existing setup but sometimes I think self hosting using WriteFreely would be easier and offer better integration with the Fediverse.

Stung by the criticism of our Football Web Pages APEX application, we decide to try to address the various issues raised by end users and peer code review.

1. When clicking ‘Fixtures’, I get ‘ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request’
2. If you enter a Competition only, it works fine. If you enter a Team only, it works fine. If you enter both a ‘Competition’ and ‘Team’, the results look weird. Should ‘Team’ be a cascading LOV based on the ‘Competition’ ?
3. The column names and labels need tidying up. There are a lot of meaningless ID fields displayed.
4. It would be nice to have the option to review past results separately from fixtures in the future.
5. Performance - the Popup LOV’s for Competition and Team are sluggish. Why are they so S L O W ?
6. The navigation menu looks chaotic and ugly.

HTTP 400 error entering Fixtures report¶

This error is because the REST parameter for 'Competition' or 'Teams' is required. When we first run this page, there no values defined for the 'Competition' or 'Teams' parameters which results in the error.

A quick and easy solution is to simply default the competition to 'Premier League'.

Edit the 'Fixtures' page. This is Page 8 in my example. The page number may be different in your APEX environment.

FWP Fixtures

Edit the 'P8_COMPETITION' parameter.

Competition parameter

Filter on 'Default' and configure a default value with 'Type' set to 'Static' with the value set to '1' (Premier League).

Competition parameter default

Save and test the changes. Now if you enter the 'Fixtures' page for the first time, you should see data displayed for the Premier League (instead of the HTTP 400 error).

Competition defaulted

This may not necessarily be the best solution to this problem. For example, I would like the 'Competition' to default to 'Isthmian League - South Central' or the team to default to 'Kingstonian FC'. This requirement could be implemented using a user profile page.

Parameter validation¶

Defaulting the 'Competition' to Premier League may appear to have resolved the issue when neither of the 'Competition' and 'Team' parameters are supplied. However, you can easily reproduce the original issue by selecting the 'Select' value for each parameter and clicking 'Go'.

APEX provides a declarative solution for parameter validation which we can use to resolve this issue.

Navigate to the 'Fixtures' page in App Builder.

Fixtures validation

Click the 'Processing' tab.

Create a Validation called 'ValidateParams'.

Click on 'Validating', then right-click and select 'Create Validation'. Scroll down to the 'Validation' section with Type = 'Expression' and Language = 'PL/SQL'.

Fixtures validation create

I always forget the semantics of the validation expression so I normally click the 'Help' tab in the middle pane for a quick reminder.

To pass the validation, and not raise an error message, when the employee is in department 30 or is a manager:

( :P2_DEPTNO = 30 or :P2_JOB = 'MANAGER' )

In our case, the two parameters are an exclusive OR. The user must either select a Competition or a Team but not both.

Therefore, our validation expression (which specifies when the parameters are valid) is:

(:P4_TEAM is null and :P4_COMPETITION is not null)
or
(:P4_TEAM is not null and :P4_COMPETITION is null)

APEX insists you enter a meaningful error message to the displayed to the end user if the validation fails.

You must select a competition or team.

Test the changes and check the user now gets an error message displayed.

Fixtures validation message

A lot of the FWP API's require this identical combination of parameters. Instead of copying and pasting this PL/SQL block, put it into a stored procedure, so you can reuse this logic. Then, if this logic subsequently changes in the future, you only have to change the code in one place.

Summary¶

We have (finally) addressed a couple of issues originally raised in October 2022.

This is the antithesis of the Agile development methodology but we will endeavour to address the remaining feedback during the course of 2025.

Your Main Content¶

This is where the magic happens. Write your awesome content here!

Here is the wonderful second paragraph.

Standard text markup¶

bold

italic

underline

Verbatim

code

StrikeThrough

Lists¶

Unordered list

• Apples
• Oranges
• Pears

Ordered list

1. First
2. Second
3. Third

Blocks¶

Sample quote

Everything should be made as simple as possible, but not any simpler --Albert Einstein

Example code block

#include <stdio.h>

int main(int argc, char *argv[])
{
 printf("Hello world"):;
}

Tables¶

Sample table

Hyperlinks¶

External

BBC

Images¶

Example image

Non League football

Misc rarely used niche markup¶

Subscripts and Superscripts example. Superscripts need some additional orgmode configuration but I don't care.

orgmode sub and superscripts

The radius of the sun is R_sun = 6.96 x 10^8 m. On the other hand, the radius of Alpha Centauri is R_{Alpha Centauri} = 1.28 x R_sun.

Footnotes

The Org website¹ now looks a lot better than it used to.

Flushed after the success of getting GoToSocial 0.5.2 working successfully, I was about to celebrate with a cup of tea when this post appeared in my timeline about the release of 0.6.0 (Release Candidate 1).

Good morning everyone! We just cut the first release candidate for v0.6.0 of GoToSocial: v0.6.0-rc1.

There's a preposterous amount of changes included in this one (we'll do a proper organized list when we do the release proper).

If you feel adventurous and up for helping us out with finding any bugs/regressions in the release, please have a go on the release candidate and let us know if you find any weirdness :)

Funnily enough, the upgrade process was one I had considered and was keen to understand so obviously, I just leapt in and attempted the upgrade.

# systemctl stop nginx
# systemctl stop gotosocial
# mv /opt/gotosocial /opt/gotosocial-0.5.2
# mkdir /opt/gotsocial
# cd /opt/gotosocial
# wget https://github.com/superseriousbusiness/gotosocial/releases/download/v0.6.0-rc1/gotosocial_0.6.0-rc1_linux_arm64.tar.gz
# tar xvf gotosocial_0.6.0-rc1_linux_arm64.tar.gz
# cp ../gotosocial-0.5.2/sqlite.db .
# cp ../gotosocial-0.5.2/config.yaml .
# mkdir storage
# cp -rv ../gotosocial-0.5.2/storage /opt/gotosocial
# chown -R gotosocial:gotosocial /opt/gotosocial
# systemctl start gotosocial
# systemctl status -l gotosocial
# systemctl start nginx

Then I examined the GoToSocial log file to see if the database migrations had been executed OK.

timestamp="29/11/2022 11:38:43.364" func=bundb.sqliteConn level=INFO msg="connected to SQLITE database"
timestamp="29/11/2022 11:40:01.473" func=bundb.doMigration level=INFO msg="MIGRATED DATABASE TO group #2 (20221006114842_add_rss_functionality, 20221011125732_refetch_updated_emojis, 20221031145649_emoji_c\
ategories, 20221108142419_create_account_tombstones)"
timestamp="29/11/2022 11:40:01.479" func=bundb.(*adminDB).CreateInstanceAccount level=INFO msg="instance account footy.social already exists"
timestamp="29/11/2022 11:40:01.479" func=bundb.(*adminDB).CreateInstanceInstance level=INFO msg="instance entry already exists"
timestamp="29/11/2022 11:40:01.479" func=concurrency.NewWorkerPool level=INFO msg="worker.Worker[messages.FromClientAPI] created with workers=4 queue=400"
timestamp="29/11/2022 11:40:01.479" func=concurrency.NewWorkerPool level=INFO msg="worker.Worker[messages.FromFederator] created with workers=4 queue=400"
timestamp="29/11/2022 11:40:01.487" func=concurrency.NewWorkerPool level=INFO msg="worker.Worker[*media.ProcessingMedia] created with workers=4 queue=40"
timestamp="29/11/2022 11:40:01.487" func=concurrency.NewWorkerPool level=INFO msg="worker.Worker[*media.ProcessingEmoji] created with workers=4 queue=40"

Login to the GTS Admin interface to check the version and, much to my surprise, success. The GTS version in the footer is 'GoToSocial 0.6.0-rc1 git-97f5453'.

I like experimenting with software and technology.

Many years ago, I built a Laconica instance. Not because I needed a Laconica instance but because I was curious and any knowledge gleaned would be useful. Standard LAMP stack. Same as the WordPress blogging software which I had already built.

Plus Laconica releases were named after R.E.M songs by Evan.

Similarly, I got an account on mastodon.sdf.org in preference to Twitter because I favour OpenSource software and the underdog.

When I discovered there were self-hosted alternatives to Mastodon, I simply couldn't resist and acquired a domain name, commodity hosting with Digital Ocean and built a single-user Pleroma instance.

This was an interesting exercise as Pleroma is supposedly less resource heavy than Mastodon and is implemented in Elixir (a programming language unfamiliar to me).

I followed the documentation, installed and configured Pleroma. Then I occasionally monitored the system load and the Postgres database size. Pleroma was rock solid for 18 months. Much to my surprise, I even managed to upgrade the software with no issues.

However, when I discovered that Pleroma was unable to follow my old friend David Marsden's micro.blog using the standard ActivityPub protocol, serious action needed to be taken.

I work for Oracle who offer an AlwaysFree tier which includes an ARM instance (1 CPU, 6GB) running Ubuntu 22.1. I was curious to explore this avenue as, again, knowledge is useful.

Originally, I had lofty ambitions to host a Federated instance aimed at a community of folk interested in football so we could have endless, tedious discussions and banter without pestering everyone else.

The various stories about the moderation commitment and performance and scalability issues for large (or even medium sized) sites slightly made me pause for thought. If you make a commitment, you should honour it.

I use Hugo for this blog which is written in Go and a single executable so GoToSocial piqued my interest as this is a similar architecture. Single Go executable with decent documentation, helpful community and fairly straightforward configuration.

After a few glitches with the Nginx integration and failing to read the documentation carefully enough, I had a GoToSocial instance up and running !

GoToSocial doesn't include a front-end GUI but I soon got used to the Pinafore (clean, single column) interface.

It was a pity that GoToSocial doesn't currently support import of 'Friends'. However, as I was only following 100 people, it was an opportunity to manually review and trim that list.

Please remember that GoToSocial is 'Alpha' software and the current limitations are well documented but the pace of development is rapid.

It's early days but I'm enjoying my first experience of GoToSocial and I like the fact that GoToSocial supports both Postgres and sqlite for the database.

In the last article, we created a simple APEX application fetching data about English football from the Football Web Pages site which provides an authenticated REST API.

However, all I really want to do is to quickly look at Kingstonian's forthcoming fixtures for the next month. Fortunately, there is an FWP API providing that information.

Endpoint: https://football-web-pages1.p.rapidapi.com/fixtures-results.json

• Matches - The current list of matches for a competition/team
• The following parameters may be set:
  • comp - The ID of the competition (note: one of "comp" or "team" is required)
  • team - The ID of the team (note: one of "comp" or "team" is required)

We already have created an APEX report listing all the available Competitions (including the numeric ID values) so it would be useful to have a similar report listing all the Teams.

Teams¶

Endpoint: https://football-web-pages1.p.rapidapi.com/teams.json

• Teams - A list of the teams covered
• The following parameters may be set:
  • comp - The ID of the competition

Create a new REST Data Source called 'FWP Teams' with this endpoint using the 'Football Web Pages' authentication method.

Create a new page 'Teams' with an Interactive Report fetching data from this REST Data Source.

Search for 'Kingstonian' to find out the numeric identifier for this club (236)

Fixtures¶

REST Data Source¶

The Fixtures API can take a query parameter which is either 'competition' or 'team'.

Thus far, we have only used a 'Header' to supply the credentials to access the API.

API query parameters are name-value pairs introduced by a '?'. This is analogous to database queries which filter the results returned by a SQL query in the 'WHERE' clause.

select *
from emp
where name = 'JONES';

The APEX fixtures page should support both of these API query parameters ('competition' and 'team') and enforce that either one or the other is supplied.

Create a REST Data Source for 'Fixtures'

• Rest Source Name: FWP-Fixtures
• Endpoint: https://football-web-pages1.p.rapidapi.com/fixtures-results.json
• Authentication: Football Web Pages

If you click 'Discover', APEX will return an error

ORA-20987: No data found in uploaded file.

This is because the REST Data Source is expecting a parameter and we have not specified one. To fix this, click 'Advanced' and add the required parameter.

• Parameter Type: Query String variable
• Parameter Name: team
• Value: 236 (Kingstonian)

Leave the HTTP method as the default (GET) and click 'Discover'. APEX should display a list of Kingstonian's results and forthcoming fixtures.

Add a second query parameter named 'comp' with no value.

Apply the changes to this REST Data Source.

Fixtures Page¶

Navigate back to Application Builder and create a blank page called 'Fixtures'.

Search region¶

Create a region named 'Search'. Change the template of the 'Search' region to 'Collapsible'. The region template is in the 'Appearance' section.

Create two page items on the Search region

1. Competition
2. Team (uncheck 'Start New Row')

Create a button named 'Go' with the default action of 'Submit Page'. Check the 'Hot' checkbox under 'Appearance'.

Report region¶

Create a new region called 'Fixtures'. This is simply an Interactive report based on the REST source 'FWP Fixtures'.

Run the page. Hopefully, your screen should vaguely resemble this.

Clearly, this is just a checkpoint and not fully functional yet but I like to 'release early and release often' (if only to myself).

Joking apart, this approach is actually useful to demonstrate to an end user what the APEX UI will look like and how you can use standard APEX functionality to search and filter within the Interactive report.

No need to spend days mocking up wire frames of the proposed UI. In APEX, you can present a meaningful prototype using APEX early on.

Plus you can endlessly argue over the title, size, placement and colour of the 'Go' button.

Return to the 'Fixtures' page in Application Builder. Expand the 'Fixtures' region. You will see that APEX has helpfully added a section called 'Parameters'. Expand this and you will see the two query parameters for this REST Data Source exposed here.

Edit the 'comp' parameter. Under 'Value', change the Type to 'Item' and select 'P4_COMPETITION' from the pop-up menu.

Similarly, change the value for the 'team' parameter to the APEX page item P4_TEAM.

Save and run the page. You get an error but this is expected as we have not supplied any APEX page parameters (yet).

Enter '236' into the Search region for 'Team' and click 'Go'. You will see Kingstonian's results and fixtures displayed.

Check this is actually working by changing the team to '1'. This will display results and fixtures for 'Arsenal' in the English Premier League.

This looks promising. APEX truly is a low code solution.

Now let's look at the 'Competition' parameter.

Enter '2' for 'Competition'. This is for the English Championship (neither Kingstonian nor Arsenal play in this league).

Nullify the 'Team' parameter and click 'Go'.

List of Values¶

This report is improving but needs more work. End users typically don't know that they have to enter '1' to get data for Arsenal. The 'Competition' and 'Team' parameters are clearly List of Values so we will implement that now.

Navigate to 'Shared Components' and add the following List of Values for 'Competitions'

• Name: Competitions
• Type: Dynamic
• Data Source: REST Data Source
• Rest Data Source: FWP Competitions
• Return Column: ID
• Display Column: FULL_NAME
• Default Sort: ID

Once an APEX application has a REST Data Source available, it is available to all components (LOV's, reports etc) - just like a conventional local database table.

Create a second LOV for 'Teams'

• Name: Teams
• Type: Dynamic
• Data Source: REST Data Source
• Rest Data Source: FWP Teams
• Return Column: ID
• Display Column: FULL_NAME
• Default Sort: FULL_NAME

Navigate to the 'Fixtures' page and change the page items to use the newly created LOV's.

Change the type of P4_COMPETITION to 'Popup Lov'

Under 'List of Values', select

• Type: Shared Component
• List of Values: FWP-Competitions
• Display Extra Values: Unchecked
• Display Null Value: Checked
• Null Display Value: - Select -

Repeat this process for the P4_TEAM page item using FWP-TEAMS as the LOV.

Run the page. This looks better. Now we can select a Competition and a Team correctly.

You gleefully share your V2 prototype with a colleague for peer code review and her feedback is as follows:

1. When clicking 'Fixtures', I get 'ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request'
2. If you enter a Competition only, it works fine.
3. If you enter a Team only, it works fine.
4. If you enter both a 'Competition' and 'Team', the results look weird. Should 'Team' be a cascading LOV based on the 'Competition' ?
5. The column names and labels need tidying up. There are a lot of meaningless ID fields displayed.
6. It would be nice to have the option to review past results separately from fixtures in the future.
7. Performance - the Popup LOV's for Competition and Team are sluggish. Why are they so S L O W ?
8. The navigation menu looks chaotic and ugly.

In the next article, we will try to address this valid feedback.

Introduction

The last article provided a quick introduction to REST APIs. Now we will use a simple REST API to develop an APEX application using a real world example.

Football Web Pages

I enjoy watching football (soccer). My local team are Kingstonian FC, a non-league team in South West London. Kingstonian play in the seventh tier of English football. Kingstonian's players are semi-professional so the players hold down jobs and train and play part-time.

Football Web Pages (FWP) is an excellent site for all things related to football. The site includes news, fixtures, results for all English leagues (including non league) and the European leagues. I recently noticed FWP provides a REST API.

FWP API¶

Reviewing the FWP API, the first thing to note is whether the API is public (i.e. freely available) which it is and whether it requires authentication (it does).

To access our data you must subscribe to one of our pricing plans (which include a free plan) via Rapid API at the following address:

rapidapi.com/football-web-pages1-football-web-pages-default/api/football-web-pages1

Authentication

When you subscribe via Rapid API you will be given a key, and you must provide this in a header named "X-RapidAPI-Key" with every request.

A lot of API's provided by larger sites offer a facility to issue API calls directly on the site. This enables the developer to examine the specification of the API and experiment with different headers, query parameters and examine the response data in various formats.

FWP doesn't offer this functionality but it's a relatively simple API so we can use Insomnia to experiment with the API.

Normally, I choose the simplest API available - one with no query parameters or headers (other than required for authentication).

For FWP, the 'Competitions' API looks like a decent candidate

Competitions

A list of the competitions covered

The following parameters may be set:

include: One or both of: rounds, teams (default: neither)
Endpoint: https://football-web-pages1.p.rapidapi.com/competitions.json

I'm lazy and so are you so you just enter this endpoint into Firefox. You are thwarted.

The FWP REST API does indeed require authentication so we need Insomnia.

Firstly, we create a folder to store all our FWP API requests. Name the folder 'Football Web Pages'.

Select the newly created folder and click 'click to add first request'.

Double click on the 'New HTTP Request' on the panel on the LHS. Rename this request to 'Competitions'.

Now enter the FWP API endpoint into the GET section in the middle panel. The endpoint (URL) is:

https://football-web-pages1.p.rapidapi.com/competitions.json

Click 'Send'. You get the same authentication error. You feel thwarted and disappointed but this is OK. You haven't provided your credentials yet but the endpoint is correct and the FWP server correctly responded with a '401 - Unauthorized' error.

This API requires that the API key (password, credentials) are supplied in the 'Header' of the API request.

Click on the 'Header' tab in the middle section

Add 'X-RapidAPI-Key' as the 'New Header'. Then add your private API key as the 'Value'. Remember that API headers are simply Name-Value pairs.

Click 'Send'. There is no need to explicitly save the changes to the Headers.

Finally. Success !

Look at the results in the panel on the RHS.

The API request returned a status of '200' (success). The elapsed time for the API request was 213 milliseconds and returned 10KB of data.

FWP APEX application

This demo was created and tested on Oracle's AlwaysFree tier. However, it should also work fine on Oracle's hosted APEX service on apex.oracle.com or a local APEX instance.

Navigate to App Builder

• Click 'Create a new App'
• Click 'New Application'
• Name the application 'Football Web Pages'
• Accept all the default options.

First, we need to configure the Web credentials in APEX to access the FWP REST API's

In APEX, Web Credentials are shared across the workspace. Click 'App Builder - Workspace Utilities - All Workspace Utilities'

Click 'Web Credentials'

Click 'Create'

Enter the following values for the parameters

• Name: Football Web Pages
• Static Identifier: FWP
• Authentication Type: HTTP header
• Credential Name: X-RapidAPI-Key
• Credential Secret: secretapikey
• Comments - FWP API key added on 16 October 2022

The reason I always add the comments field is that many API keys have a limited lifetime (6 months or a year) for security reasons. Often it is useful to know when the client secret was created.

Click 'Create' to save the changes

Next, create a REST data source for the FWP REST API

Navigate to 'App Builder' and click 'Shared Components'.

In the bottom left section, click 'REST Data Sources'.

Click 'Create'

Select 'From scratch' for 'Create REST Data Source' and click 'Next'

Leave the default of 'Simple HTTP' for the value of 'REST Data Source Type'

Enter 'FWP-Competitions' for the 'Name'.

Enter 'https://football-web-pages1.p.rapidapi.com/competitions.json' for 'URL Endpoint'

Leave the optional parameter 'HTTPS Host Name' blank.

Click 'Next'

Leave 'Create New' for the 'Remote Server' parameter

Accept the values helpfully supplied by APEX for 'Base URL' and 'Service URL Path'.

Click 'Next'

Accept the default of 'No Pagination' for 'Pagination Type'.

Click 'Next'

Ensure 'Authentication Required' is checked and select 'Football Web Pages' from the drop-down menu for Credentials.

Click 'Discover'.

APEX has helpfully sent this API request to the FWP server using the Web credentials and provided us with a preview of the data set returned so we can check it looks correct.

Wizards might want to click 'More Detail' but this looks good enough for us to just click 'Create REST Data Source'.

Now we have defined Web credentials and created a REST data source, let's finally create an APEX page displaying the Competitions.

Navigate back to 'App Builder' and select the 'Football Web Pages' application.

Click 'Create Page' and 'Interactive Report' from the Page Wizard.

Click 'Next'

Enter 'Competitions' for the name of the new page.

Under 'Data Source', select 'REST Data Source' and select 'FWP Competitions' from the drop-down menu.

Click 'Create Page'

Run the 'Competitions' page

Summary

That took a while but we have created an APEX application that fetches data from a REST Data Source that requires authentication.

These are valuable building blocks to refine and extend this APEX application when we explore a range of different API's.