Oracle External Table Pre-processing – Soccer Super-Powers and Trojan Horses

The end of the European Football season is coming into view.
In some leagues the battle for the title, or against relegation is reaching a peak of intensity.
Nails are being bitten throughout the continent…unless you are a fan of one of those teams who are running away with their League – Bayern Munich, Juventus, Celtic…Luton Town.
In their fifth season since relegation from the Football League to the Conference, Luton are sitting pretty in the sole automatic promotion place.
Simon is desparately attempting to balance his “lucky” Christmas-cracker moustache until promotion is mathematically certain. Personally, I think that this is taking the concept of keeping a stiff upper-lip to extremes.

"I'll shave it off when we're definitely up !"

“I’ll shave it off when we’re definitely up !”

With the aid of a recent Conference League Table, I’m going to explore the Preprocessor feature of External Tables.
We’ll start with a simple example of how data in an External Table can be processed via a shell script at runtime before the results are then presented to the database user.
We’ll then demonstrate that there are exceptions to the rule that “Simple is Best” by driving a coach and Trojan Horses through the security hole we’ve just opened up.
Finally, in desperation, we’ll have a read of the manual and implement a more secure version of our application.

So, without further ado… Continue reading

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

Born Before Computers – fogey foibles and forsight for the humble insert

Apparently, I am considered by some to be stuck in my ways.
For example, Deb now refuses to stand with me in the supermarket queue because of my tendency to argue with the automated checkout.

This even extends into my working life where I have a colleague who is a bit more hip and with it when it comes to writing code.
The rest of us in the Oracle team – slightly older than this person it must be said – are apparently “BBC”.
This isn’t some reference to the venerable British Broadcasting Corporation, nor even to the BBC micro which was popular back in the 80′s.
Evidently, it is something of a disadvantage to have been “Born Before Computers”.
I’ll confess, I do write most of my database code in a text editior and run it via SQL*PLUS. Whilst I use an IDE for looking at stored database code, I’m not that keen on using it as a code environment.
As is usually the case, sometimes things are done in a certain way for years because that’s the best way to do it…and sometimes it’s simply because “that’s how we’ve always done it”.
The trick is, knowing which is which.

All of which brings us to the INSERT statement. Continue reading

Oracle Batch Job Logging – a framework for domestic harmony

Like most men, I have a standard of tidiness and cleanliness that I think of as “Bloke Clean”.
Deb’s standards are rather higher ( she would say normal). The difference can occasionally be a source of tension.
“Maybe I’ll just run off with that Steven Feuerstein bloke !”, she may have said during one of our discussions about the state of the study.
“Oh really ?”, I might retort, “and what’s he got that I haven’t ?”,
“Money, fame, talent and his own framework…not to mention a cleaner, I’ll bet”.
“Well…at least I have more hair”, I might say, disconcerted by her surprisingly comprehensive knowledge of someone who, it must be said, is not exactly famous outside of the wonderful world of Oracle.
“Not by much.” would probably have been the devastating reply.
Predictably, a compromise has now been reached…the upshot of which is that I’ve just spent the afternoon becoming intoxicated by the fumes from various cleaning products…and the study is now gleaming and all the papers filed away…and we’re getting a cleaner.
As for the money, fame and talent…well, I’ll just have to make do with the framework for now.
Truth be told, calling it a framework is overstating things a bit. But hey, it does give me an excuse to come up with a (possibly) amusing name. Continue reading