ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.
Continue reading

Oracle’s hidden documentation – Commenting the Data Model in Oracle

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain. Continue reading

Tracing for fun and (tk)profit

If you ever wanted proof that time is relative, just consider The Good Old Days.
Depending on your age, nationality, personal preferences etc, that time could be when rationing finally ended; or when Trevor Brooking won the Cup for West Ham with a “bullet” header; or possibly when Joe Carter hit a three-run homer to seal back-to-back World Series for the Blue Jays.
Alternatively, it could be when you were able to get on to the database server and use tkprof to analyse those tricky database performance issues.

In these days of siloed IT Departments, Oracle trace files, nevermind the tkprof utility are out of the reach of many developers.
The database server itself is the preserve of Unix Admins and DBAs, groups which, with good reason, are a bit reluctant to allow anyone else access to the Server at the OS level.

Which is a pity. Sometimes there is just no substitute for getting into the nitty gritty of exactly what is happening inside a given session.

For those of you who miss The Good Old Days of tkprof, what follows is an exploration of how to access both trace files and even the tkprof utility itself without leaving the comfort of your database.
I’ll go through a quick recap of :

  • how to generate a trace file for a session
  • using tkprof to make sense of it all

Then, coming bang up to date :

  • viewing a trace file using an external table – and why you might want to
  • Using a preprocessor to generate tkprof output
  • implementing a multi-user solution for tkprof

Continue reading

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

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