Posts from January 2025

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 Quirks Blog Questions Challenge, so heres 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.