Using the INTERVAL data type ( and some pictures) to tell the difference between two Timestamps in Oracle

Looking after an overnight batch process can be a big fraught at times.
If it’s a good day, you might begin the morning with a nice cup of coffee and a leisurely scroll through the logs to confirm that all is well.
In contrast, if the batch has overrun you may well find yourself scrambling through those same logs whilst gulping down large quantities of caffeine in a desperate attempt to hot-wire your brain into working out the elapsed time between each entry. Not great. Especially when you consider that, as Terry Pratchett put it,
“Coffee is a way of stealing time that should by rights belong to your older self”.
A better approach might be to get Oracle to do it for you.

What we’re going to look at here is :

• the INTERVAL data type that holds the difference between two TIMESTAMP values
• using the LAG SQL function to report the interval time between timestamps across rows of a query
• creating some graphs using SQLDeveloper User Defined Reports for when it’s just too early for words and numbers.

Capturing DBMS_OUTPUT.PUT_LINE messages in the heat of a Six Nations campaign

It’s not a good time to be English, not in my house. That’s right, the Six Nations is in full swing and my Welsh wife and her family are once again indulging in the time-honoured practice of taunting the English.
As deployment of my emergency backup nationality ( “born In New Zealand, I’m neutral, honest !”) has failed, I’m reduced to keeping a low profile until the Brains Bitter runs out.
Fortunately, there’s excellent Wi-Fi reception in the cupboard under the stairs, so I can drown out the sound Welsh Nationalism and concentrate on the topic at hand.

Whatever language you’re coding in, however sophisticated you’re IDE, sooner or later you will likely revert to the time-hounoured debugging technique of writing out messages or variable values at runtime in an effort to find out what the heck is going on.
PL/SQL is no exception and DBMS_OUTPUT.PUT_LINE is the procedure that facilitates this practice.
However, whilst it’s a simple matter to view the messages interactively, how can you retrieve them if you are running the program as part of a batch process, where attaching a client to the session is not an option ?

WARNING – the code that follow may contain (memory) leeks…

Fantasy Software Development Sprint 1- Coding like it’s 1889 !

Now we have our backlog, it’s time to finish our setup and then get on and knock out some code…or maybe not.
Remember, we’re using APEX as part of our technical solution and we’re going to let it do some of the heavy lifting, at least to start with.

What we’re going to cover here is :

• The stories selected for the Sprint
• APEX attribute setting required to insert records into a table containing an identity column
• What our Physical data model looks like at the end of the Sprint

But first…

Why Fantasy Software Development – A quick recap

The reason for this series of posts is to explore how both the methodology used for development and later enhancements to the application affect the finished product.
The requirements for this initial version of the application cover Football only up to the end of the 1888/89 season. For the purposes of this exercise, we’re trying – for the most part – not to anticipate developments after that date. So, whilst we may reasonably take steps to future-proof our application, we’re not going to indulge in clairvoyance by predicting the advent of VAR, for example.

Right…

Fantasy Software Development – Story Finding

As threatended promised, I’m going to look at the first phase of my Application – recording the results and outcomes of Soccer tournaments from the first days of the sport in 1871 through to the culmination of the first Football League Championship in 1889.

I’ll begin with a narrative description of the functional requirements of our application. OK, it’s more like a potted history of the early days of Association Football, but I’ve got to start somewhere.

I’ll use this to extrapolate some user stories, which I’ll then drop into Altassian Jira having taken advantage of a free Jira account.

If you’re an Oracle developer and reading this is your first experience of Scrum, then you may feel that it’s not an obvious fit for developing a data-centric application.
On the other hand, if you’re a Scrum officianado, you may be faintly horrified by the “free-form” way I’m approaching Scrum. So, something to annoy everyone then…

Fantasy Software Development – exploring Brownfield development with Oracle RAD

I just can’t get the hang of Fantasy Football.
I’ll do all the right things, like swapping out striker A because of that tough away game coming up and bring in striker B who is bang in form and playing against a team struggling at the foot of the table.
Then I’ll look on helplessly as striker A bangs in a hat-trick whilst striker B scores an own-goal and manages to get sent-off.
Therefore, I’ve decided to stop worrying about how my pretend football team is getting on and do something a bit more useful, like undertaking a pretend development project. Yep, welcome to Fantasy Software Development.
The primary reason for doing this is to explore how an application evolves over it’s lifetime.
A huge amount has been written about how to develop applications from scratch but not so much about how they grow once the initial release is in production and the enhancement requests start coming in.
In other words, how is it that an application which, if you were writing it from the ground up right now would look like this :

…actually looks like this :

This is quite a sprawling project and my intention is to tackle it through several posts. This one is simply to give and overview of where I might be headed.

Horrible Histograms and Invidious Indexes – Exploits to by-pass Oracle Database Security – including VPD and Database Vault

This article is about how Indexes and Histograms may be used to view data to which a user does not ordinarily have access. It includes details of how these objects can be used to circumvent both Virtual Private Database and Database Vault restrictions.
If you want a serious and thorough exploration of these issues then I’d suggest you take a look at Alexander Kornbrust’s recent DOAG Presentation.

However, if you like your infosec to come with cute puppy pics, read on…

Assaulting my Battery – Myths and Legends about looking after a laptop battery.

I’m not a hardware specialist, but I’m not averse to prizing open a laptop case occasionally when the need arises. Most of the time.
This, however was an exception. This is one view of my XPS13 that I never wanted to see :

What brought me to this point was the untimely demise of the battery.
What follows is not a blow-by-blow account of how I changed the battery, with all of the attendant nervousness that practically everything I was looking at was rather expensive and no doubt quite important.
Incidentally, if you are looking for that sort of thing then there are several helpful videos available, like this one, for example.
Rather it is an exploration of some of the advice around about ensuring your battery has a long and happy life. Specifically, it is an attempt to divine which advice is still relevant to the Lithium Ion (Li-Ion) Polymer batteries of the sort that power modern laptops…

DBMS_SCHEDULER – Fiddling with File Watchers

I recently updated my blog profile picture. Let’s face it, it was about time. The old one was taken 14 years ago.
This time, I thought I’d go for a contemporary portrait, which has the added benefit of being less likely to frighten animals and small children.
Unfortunately, it is also crying out for a caption :

Just popping into the bank. Keep the engine running

One or two alternatives captions may well crop up in the following paragraphs.
Primarily though, I’ll be talking about the file watcher capabilities of DBMS_SCHEDULER.
Introduced in 11g, it enables developers to trigger actions when a file arrives without having to leave the comfort of their database.
There are a few moving parts and getting them to work together can take a bit of, well, fiddling.
Specifically, we’ll be looking at :

• configuring the frequency at which filewatchers are run
• creating a credential to use for file watching
• creating a file watcher to determine when a file has arrived
• creating a job for the file watcher to execute
• adding a program so that we can get some information about the file
• getting the filewatcher to wait for a large file transfer to complete

Before we get into all of that, we need to have a quick chat about environments…

Time flies when you’re having fun – The truth about Oracle Exception handlers (this week)

Oracle Twitter is a bit like Poker – you tend to take notice when Aces turn up.
I mention this because I’m indebted to Jacek Gebal, Sabine Heismath and Andy Sayer, whose discussion on this topic alerted me to the fact that PL/SQL exception handling had changed in a small but important way whilst I wasn’t looking.

Just in case you’re wondering whether that opening paragraph is a prelude to a post containing gratuitous references to a certain Motorhead song, all I can say is…if you like to gamble…

Making the most of Oracle 18c XE – Pluggable Databases and the Oracle Eco-System

This was going to be a simple post about creating multiple Pluggable Databases (PDBs) in Oracle 18cXE.
But you know what it’s like, you get your lego database set out and then you spot those unusual shaped bricks… and the transparent ones… oh, that one has a wheel on…and get a bit carried away.
What follows is a guide on how to create three concurrent PDBs on an Oracle 18cXE database. However, I have taken the opportunity to investigate other areas of the Express Edition platform and so will be making use of Advanced Compression as well as Oracle Enterprise Manager Express.
In fact, I wouldn’t be surprised if SQLDeveloper put in an appearance as well.
I’ve also included a couple of the more notable members of Oracle’s small-but-perfectly-formed eco-system into the final design. On top of all that, I’ll be performing a magic trick to ensure that I only have to install each piece of software once, even though it may end up in multiple PDBs…