Finding an Identity Column Sequence in Oracle

It’s that time again. It’s the week between Christmas and New Year and I’ve been grappling with the advanced mathematics required to calculate when the next refuse collection is due.
I’ve got some code that does the calculation and inserts the collection date into a table, which I’m running on Oracle 18cXE.
Automating the test for this code is a bit tricky because the table uses an identity column as it’s key.
Therefore, I need to figure out the name of the sequence used to generate the identity value and find it’s current value in the session to identify and test the record we’ve just inserted.

What I’m going to cover here is :

• my search for the sequence associated with an identity column
• predicting the name of the identity column sequence without having to look it up
• just using a named sequence instead of an identity column but without having to write a trigger

Fortified by a traditional Betwixmas breakfast of cold sprouts and trifle, I’m ready to get cracking…

Predicting the future with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING

“Tempus Fugit !”…may well be similar to the collection of syllables that spring to mind when you find out the “fun” way that your carefully crafted DBMS_SCHEDULER calendar string doesn’t quite do what you thought it did.
Fortunately, the gift of clairvoyance is within your grasp if only you follow the Wisdom of the Ancient DBAs and their mantra of Legere mandata (Lit. “Read the instructions”)…

Side-stepping session silliness – ORA-04068 and MODIFY_PACKAGE_STATE

I’m writing some PL/SQL package. In a surprise development, I’m actually following best practice and using utPLSQL to unit test it.
Even more surprisingly, I’m following the Test-Driven Development (TDD) approach and writing my tests before my application code.
This being PL/SQL, this approach does require a fair amount of re-compilation.
I’m running in SQLDeveloper, which I’ve configured to open each worksheet in a new session ( Jeff Smith has shared the details on how to do this here) .

The Three Laws of Robotic Vacuum Cleaners and the ANYDATA Datatype

After some observation of my recently acquired robotic vacuum cleaner, I’ve concluded that Asimov’s Three Laws of Robotics require some revision :

First Law – A robot may not traverse stairs. If you want it to do the whole house then move to a bungalow.

Second Law – A robot must stop in the most inaccessible place possible, such as under a bed or a sofa, unless doing so would conflict with the First Law

Third Law – A robot must seek out and entangle itself in any loose wires/pet toys/socks it can find unless doing so would conflict with the First or Second Laws.

Mind you, these Laws are unlikely to be immutable. I mean, The Daleks never used to be able to climb stairs…until they discovered CGI…

Defaulting Argument Values in a BASH script

I recently found out (again) how to default argument values in a shell script, so I thought I’d write it down this time.
The ABBA flavour in what follows is because we’re talking about BASH – the Bourne Again SHell and Bjorn Again are an ABBA tribute band. This is quite handy because the ABBA back catalogue is rather versatile having already lent itself to an explanation Oracle password complexity.

Welcome to the inside of my head. Sorry about the mess…

Help, Oracle’s yanking my Chain (count)

Despite all the hype, it turns out that Football was coming home only to change it’s underwear before going out again.
As the Azzuri may have said, “Veni Vidi Vici (ai penalti)”…or something like that.
Look, I’m over it, there are far more important things in life than football and it will not impinge further on this post. Well, not much.

What I’m going to explore here how you persuade Oracle to report the count of chained rows for a table in the _tables dictionary views. The examples that follow were run on Oracle 19c Enterprise Edition. I have observed the same behaviour all the way back to 11gR2.

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…