Conditionally calling a script in a SQL*Plus control script

For those of us who haven’t quite gotten around to incorporating Liquibase in our database code release pipeline, we sometimes find ourselves in a situation where it would be extremely useful to be able to apply some branching logic in a SQL*Plus control script.
In order to save myself the pain of trying to figure out exactly how you can do this without writing lots of dynamic code, I’ve decided to write it down.

NOTE – the scripting techniques outlined here work the same way in both SQLCL and SQL*Plus.

What follows are examples of SQL*Plus scripts which implement branching to :

• choose which of two scripts to run
• choose whether or not to run a script

The difference between these two use cases is fairly subtle, but worth exploring…

Advertisement

Converting a non-partitioned table to partitioned in Oracle

Teddy tells me that his New Year’s Resolution is to teach an old dog new tricks.
I’ve no idea which particular “Old Dog” he has in mind.

Anyway, it looks like 2023 is going to be The Year of The “Ted(dy) Talk” on this blog, starting with the “modern” method ( i.e. in Oracle 12c and later) of convert a non-partitioned table to be partitioned.

“There’s is an easier way to do that, you know”

Such an undertaking used to require a lot of messing about, probably involving DBMS_REDEFINITION.
If you’re still stuck on 11g, you can find a good example of this method on Oracle Base

Since 12c however, things have become much easier.

Before we go on however…

License Warning

Use of partitioning may require an additional license depending on the Oracle version and Edition so it’s a good idea to make sure you’re covered before you start using it.

With that in mind…

Building a Master-Detail Drill-Down with APEX

APEX is one of those technologies that I get to use intensively for a short period of time and then don’t touch for years.
As I’ve recently acquired an OCI Free Tier Database (version 19c) and, consequently, Oracle update the APEX version for me (currently 22.3), now seems like a good time to see how it’s evolved since I last had a play.

It just so happens that I’m in need of a UI for my hastily constructed World Cup Wallchart application.

What I’m going to look at here is :

• how to use the APEX Drill-Down creation wizard to create related application pages
• how I can customize the generated pages without writing much code

Incidentally, I’ve put the database source-code for the app on Github should you wish to take a look.
Health Warning : when I say “hastily constructed”, I’m not kidding.

On the plus side, I have spent some time on the physical data model, with special attention made to the table relations.

“OK Boomer”, I hear you say, “but how’s that going to help with the UI ?” Let’s find out…

Flipping Plans ! Retrieving past execution plans from AWR

SQL is a special programming language.
It’s quite possible that I’ll be interrupted at this point by some Tech Bro arguing that SQL is not a programming language actually. The phrase “Turing Complete” may already be heading my way.
To save time, I’ll simply observe that I get paid folding money to write SQL so I don’t really care whether it lacks some the properties of Powerpoint (e.g. being Turing Complete).

As I was saying SQL is almost unique among modern programming languages in that it is a 4GL.
In other words it is declarative – you use it to describe the information you want to retrieve. It is then up to the database engine to figure out how best to fulfill your requirements.

Oracle likes to remind me of this every so often when, after months of efficient and trouble-free running, it suddenly gets bored and chooses a new and exciting execution plan which causes a query to take hours rather than minutes.

What follows is a short exploration of how we can tell this is happening and how to retrieve current and previous execution plans for a SQL statement from Oracle’s Active Workload Repository (AWR).

Before we go on, however…

Generating Journal Triggers with help from DBMS_UTILITY

It’s good to see modern working practices being implemented in Government, especially in Downing Street, where they seem to have embraced the idea of hot-desking.
Not to say there isn’t room for improvement. In the recent leadership election, candidates claimed a total of 380 nominations…from an electorate of 357. Given the state of the Economy, you do rather hope they chose the candidate that can count.

In order to distract myself from the ongoing shenanigans in Whitehall, I’m going address myself to one of the regular chores facing anyone working on a new Data Warehousing application – implementing journal triggers on your mapping tables.

Any variation on the theme of Star Schema is likely to result in numerous mapping tables, the contents of which will affect how the application behaves.
Recording DML changes to data in these tables is therefore important to maintain an audit trail, or even just to aid debugging.
Rather than writing nearly identical triggers for each of these tables, wouldn’t it be better if we could automate the process to some degree ?

As well as the usual data-dictionary lookups, I’m going to keep things interesting by :

• creating a journal table for the trigger to insert into
• ensuring consistent formatting (canonicalization) of “key” character values – by making them all uppercase
• keeping my dynamic SQL statements readable by using a template
• converting comma-separated lists to PL/SQL tables and back again with DBMS_UTILITY

DETERMINISTIC and RESULT_CACHE PL/SQL functions

Recent events here in the UK have once again emphasised the value of being careful with your “cache”.

To this end, I’m going to take a look at how PL/SQL functions defined using the DETERMINISTIC or RESULT_CACHE keywords use cache values. Specifically we’ll look at

• a simple deterministic function
• an example of incorrectly applying the DETERMINISTIC keyword
• how the return datatype determines whether values from a DETERMINISTIC function are cached
• Using a RESULT_CACHE function to persist cache values across database call

I’ll be doing all of this on an Oracle 19c database.

Just in case you’ve stumbled across this post in a desperate search for expert advice on this topic, you may find the following useful :

• If anything deserves the adjective “seminal” then it’s this presentation on Result Cache functions by Jeff Kemp
• There’s this article by Ian Hellström, which I found useful
• As ever, Oracle Base has also covered this topic in some detail

Assuming you haven’t wandered off to seek enlightenment from these Oracle luminaries, I suppose I’d better get on with it…

Saving the World from Fat-finger moments – with regexp_like

It’s not uncommon for a database application to have it’s behaviour defined, to an extent at least, by records in reference data tables.
By it’s nature, this data is static and the tables in which it resides tend to contain comparatively few rows. However, such an approach can be susceptible to erroneous data entry, especially where key values are concerned.
Having spent many an “entertaining” afternoon/evening/dead-of-night, trying to hunt down some mystery bug, only to find that one of these values includes an extraneous space or invisible control character, I’ve come to appreciate the ability of regexp_like to point these things out.
The code examples that follow should be based on some sensible data set, probably from the HR demo schema. However, Dr Fatfinger does sound rather like a Bond villain…

Linux – Checking that two files contain the same data but in a different order – Sorted!

Trying to regression test a change to a feed-file generation program can be tricky. Whether the format is CSV or some fashionable markup language, the ordering of the result set tends to be unimportant in such circumstances.
When testing, we need to verify that the files produced by the new version of the program contain the same data as those produced by the old version, irrespective of the order in which the records are written.

Recently, I was rescued from my struggle with just such a problem by my colleague, Don Thomson, who imparted some (Linux) Jedi wisdom resulting in a simple yet effective solution, involving an inventive combination of Linux utilities.

What we’re going to look at here is :

• comparing files with diff
• using sort to give diff a hand
• comparing sets of files in different directories using sum
• ignoring trailer records in delimited files using grep or head

Excluding English Public Holidays from a DBMS_SCHEDULER Schedule

This week we’re taking on the entire Establishment through the medium of… DBMS_SCHEDULER Calendar Strings.
I should probably explain that, in a normal year, England has eight Public Holidays. Four of these are based around religious festivals. Whilst Christmas Day is always on 25th December, working out when Easter falls requires some complex calculations.
On top of that, there are times when the secular holidays are re-arranged and even days added. These are usually due to some Royal anniversary or event.
What I want to explore is how we can construct DBMS_SCHEDULER calender string s which include working days ( in this case week-days) but exclude Public Holidays.
The three specific examples I’d like to end up with are :

• a schedule for the first working day of each month
• a schedule for the last working day of each month
• a schedule for the working day on or immediately after the 6th of each month ( which we’ll say is Pay day)

In order to achieve this we’ll :

• explore how the DBMS_SCHEDULER calendar syntax can help us to calculate when each holiday will fall
• write a PL/SQL function to return the date of Easter Sunday for a given year
• create a table to hold the holiday dates and also to allow any changes to be recorded
• write a procedure to maintain the table
• write a procedure to dynamically build and maintain a Schedule of Public Holiday dates
• build the three schedules we want using the Public Holidays Schedule to exclude the Public Holidays.

The code in what follows can be found in this Github repo

Connecting to an Autonomous Database in the Oracle Cloud

Look, this is a post about setting up and configuring databases in the Cloud.
Therefore, by law, I am required to make numerous references to Captain Scarlett and the Mysterons.
This is because Spectrum has an HQ called Skybase, that looks like this :

A new Oracle Cloud data centre ?

Meet Lieutenant Green. He is the entirety of Spectrum’s IT Department, responsible for the system that runs Skybase, and who therefore understands the full implications of the phrase “unplanned downtime”.
Indestructable as Captain Scarlett may be, it’s Lieutenant Green whose indispensible.

We’re going to follow the Lieutenant as he :

• creates an Oracle Autonomous Database in the Free Tier of the Oracle Cloud Infrastructure (OCI)
• connects to the new database from his local copies of SQLDeveloper and SQLCL using a Cloud Wallet
• configures the TNS settings to allow him to initiate Thick client connections from other client tools
• gets to the bottom of whether SQLDeveloper Web has been kidnapped by Mysterons

Right, S.I.G….