PL/SQL Associative Arrays and why too much Rugby is bad for you

Once upon a time, working on an IT project in a large organisation meant reams of documentation, tangles of red-tape, and then burning the candle at both ends to get everything finished on time.
Then, someone discovered this Agile thing that dispensed with all of that.
The upshot ? Well, the documentation has been reduced ( although the red-tape is still problematic).
Many large organisations now adopt an approach that is almost – but not completely – unlike SCRUM.
It is at the business end of such a project I now find myself…burning the candle at both ends.
To pile on the excitement, Milton Keynes’ own Welsh enclave has become increasingly voiciferous in recent days.
The only SCRUM Deb is interested in is that of the Welsh Rugby Team grinding remorselessly over English bodies come Sunday.
She keeps giving me oh-so-subtle reminders of the result of last year’s game, such as when picking lottery numbers :
“Hmmm, three is supposed to be lucky…but not if your English. How about THIRTY !”
“But you’re married to an Englishman”, I pointed out during one of her more nationalistic moments.
“Which makes him half Welsh !”, came the retort.
At this point, I decided that discretion was the better part of logic and let the matter drop.
As a result of all this frenzied activity and feverish atmosphere, sometimes I’ve not been quite at the top of my game.
One particularly embarassing mishap occured late one evening and involved PL/SQL Tables – or Associative Arrays as they’re called these days – and the dreaded ORA-06531: Reference to uninitialized collection.

This particular post therefore, is mainly a reminder to myself of how to initialize and (just as importantly) clear down a Collection to prevent mysterious missing or, just as problematic, additional, records ( as well as less mysterious runtime errors). Continue reading

Speed Isn’t Everything – LOG ERRORS, SAVE EXCEPTIONS and Fun in the Snow

Before the fourth one-day international, Mitchell Johnson decided to shave off his moustache.
During the fourth one-day international, Mitchell returned the less than impressive figures of 0-72 off 10 overs.
At the end of the fourth one-day international, England had finally notched a win against Australia.
The logical conclusion to draw from all of this is that Mitchell Johnson is not a regular reader of this blog.

On the plus side, I did get a couple of interesting comments from my last post about the performance differences between Log Errors and Save Exceptions.
As well as Jim Dickson’s input, Steve Feuerstein made some observations on the Toadworld site (which you can see here if you’re interested).

These comments both had a similar theme to the effect that, whilst Log Errors and Save Exceptions are similar, there are some differences beyond their relative performance.

So, the aim of this post is to take a fresh look at these two mechanisms and how they compare.
For the code examples, I’m going to step away from the horror show that has been England’s cricket tour of Australia, and focus instead on the wacky world of Reality TV.

We’ve had celebrity high-diving, celebrity ballroom dancing, even celebrity dog-training.
With the Winter Olympics almost upon us, some particularly sadistic TV executive hit on the idea of assembling a collection of celebrities, strapping a plank of wood to each foot/handing them a tea-tray…and then pushing them off the side of a mountain.
All a bit of harmless fun. After all, what could possibly go wrong ?
Having said that, the producers of The Jump did hire a couple of extra cast members to account for the remote possibility that a broken rib/collar-bone/finger-nail might render one or more of the original contestants incapacitated. Continue reading

PL/SQL is faster than SQL – Just ask Mitch.

After their comprehensive defeat at Lord’s back in June, some experts were confidently predicting that Australia would be on the wrong-end of a clean sweep in both of the back-to-back Ashes series.
Mitchell Johnson, if he was mentioned at all, was written off by all and sundry. After all, not only did he not hand homework in on time, he couldn’t be relied upon to hit a barn door, let alone a set of stumps.
Fast-forward a few months and you can see that conventional wisdom has held…to the extent that no barn doors have been dented.
Unfortunately, the same cannot be said of English pride.
Mitch and his mates have a bit of time on their hands before Australia visit South Africa next month – that nice Mr Lehman has let the class off homework – so they’re free to assist in contradicting another of those things that “everyone knows” – SQL is always faster than PL/SQL.

What we’re going to cover here (among other things) is :

  • a quick overview of the LOG ERRORS mechanism (Mitch doesn’t do any other speed)
  • a recap of the older PL/SQL SAVE EXCEPTIONS
  • performance comparison between the two with errors present
  • Explore the limits of LIMIT
  • performance comparison when no errors are present

Mitch is standing at the top of his run. A random English batsmen is quaking at the crease, so let’s get started… Continue reading

The Anti-Pattern – EAV(il) Database Design ?

Early evening TV in our house is Soap time. Deb annexes the remote control, after which we are treated to an
assortment of angry women being angry with each other in a variety of accents originating from the North of England.
It could be worse, I suppose. We could be subjected to the offering on the other main channel ( angry London women being angry at each other in accents originating from the South East of England).
Then again, either is preferrable to an angry Welsh woman being angry at you in a Welsh accent.
Ok then, how do you make a database professional hot under the collar ? Mention the EAV design pattern.

This pattern goes by many names, most commonly :

  • EAV – Entity-Attribute-Value
  • OTLT – One True Lookup Table
  • Open Schema
  • Diabolically Enticing Method Of Data Storage (DEMONS)

OK. I made that last one up.
It is with some trepidation ( and having donned precautionary flame-proof underpants) that I am embarking on an exploration on the nature of EAV and whether it can ever be appropriate for use in a Database. Before we go any further though, I’d like to take a moment to clarify exactly what the term “database” means in the context of this discussion
Continue reading

The Movember Ashes Curse and PL/SQL Parameters

A wise man once said that those who fail to learn the lessons of history are doomed to repeat them.
Time for a quick history lesson…
England’s recent supremacy in Ashes contests have a commmon theme running through them. In none of the last three series has any Australian fast bowler sport any facial hair.
The bristling tache has been a feature of potent Australian attacks since time immemorial.
From Frederick “The Demon” Spofforth sporting the face furniture that inspired an England batting collapse which gave birth to the Ashes legend, through Dennis Lillee and (occasionally) Jeff Thompson, the tache reigned supreme.
Having reached an exuberant zenith whilst working with Merv Hughes, the tache took a lower profile for a time as the top-half of Jason Gillespie’s beard.
There was a time during the 80’s when England seemed to have cottoned on to the power of the tache and managed to fight fire with fire with notable contributions evident on the upper lips of Botham, Gooch and Lamb.
More recently however, the moustache’s mystic ability to make an English batting line-up do a passable impression of a deck-chair seemed to have been lost in the mists of time.
Then along came Mitchell Johnson’s Movember Mush Mantle.
Johnson himself has quite a respectable test record, but it seems that he may have re-discovered the secret power of the mighty moustache.
As we’ve got a couple of weeks before the second test begins, the moustache has a bit of time on it’s handlebars so has kindly agreed to illustrate the fun and games to be had with assigning default values to PL/SQL parameters.
Continue reading

Disabling the Oracle Diagnostic and Tuning Pack APIs – If you want something done, do it yourself

At last, we have reached the final episode of the Star Wars themed odyssey through the tangled web that is Oracle’s Diagnostic and Tuning Pack licensing.
Just as well really, Deb has flatly refused to give over any more evenings to my “research” – i.e. re-watching all of the films. Even the appeal of Ewan MacGregor’s Alec Guiness impression has now waned.
Just to recap then, so far I’ve looked at :

Now, finally we’re going to have a look at how we can minimize the chances of an errant select statement causing a whole heap of trouble.
Yes, we’re going to have a go at disabling access to the Diagnostic and Tuning Pack APIs without (hopefully), breaking anything.
Continue reading

Oracle Schema Differences – keeping up with the Prefix Pixie

Saturday 11th September 1976. That was the day that my Dad first took me to see the (occasionally) mighty West Ham United.
The opponents, the rather more often mighty Arsenal.
I still have vivid memories of that game. The noise from the crowd. The fact that the grass looked so green, brighter than on the TV.
West Ham not playing very well. Frank Stapleton putting a bit of a downer on the day by having the temerity to score twice in a 2-0 win for the Gunners.
My Dad recently celebrated his 70th birthday.
His present from his first-born son ? A trip to see the (previously) mighty Luton Town take on the ( probably must have been from time to time) mighty Nuneaton Borough.
Now, this may seem poor reward for my dear old Dad – he takes me to see two of the top teams in the country and he gets the Blue Square Premier League in return.
Additionally, these days it’s less the colour of the grass that assaults the senses than the colour of the boots.
These are various flourescent colours, virtually none of them black.
Mind you, as Deb pointed out, if you play for Luton and spend most of your working life dressed in bright orange, then accessorising must be a bit of a challenge.
The game itself however, is another matter.
Typical English Football – very quick, lots of commitment. You can tell it’s not the Premiership by the absence of millionaires rolling around the floor in apparent agony because they’ve broken a finger-nail.
Two late goals sends the Hatters home happy.
All of which has nothing to do with the subject of this post, apart from my choice of examples.
Comparing the table structure between different schemas is standard functionality for any self-respecting IDE. However, things get a bit more tricky if you’ve had a visit from the Prefix Pixie. He, she (or it if you’re table relationship diagram dropped out of a design tool) thought it’d be a good idea to give the same prefix to every table in the schema.

The result of this is that the tools in the IDE can’t recognize that tables with different names are meant to have identical structures.
So much for the “Premiership” of Database Development, it looks like we’ll just have to do a bit of D.I.Y. to see through the poxie pixie dust.
Dad would approve. Continue reading