Triggers on Views in SQLDeveloper

So, you’ve noticed that SQLDeveloper ( any version prior to 2.1.1) doesn’t show triggers on views. At the same time SQLDeveloper 2.1.1 doesn’t show package bodies unless you have a very highly privileged account.
If you’re determined to press-on with 2.1.1, you can see a workaround for the package body problem here.
If however, you’d prefer to wait until they’ve ironed out this particular kink before taking the plunge, but want a solution to the invisible triggers, read on … Continue reading

Unable to See Package Bodies in SQLDeveloper 2.1.1

After many happy months spent sauntering contentedly through the database, I recently came across a curious little bug in SQLDeveloper 1.5.4 where the Triggers on a View are not displayed in the appropriate Tab.
Not to worry, it’s about time I upgraded to 2.1.1 anyway. Or so I thought. I should have known – it’s the summer and bugs are everywhere.
Incidentally, if you need a workaround for the Views issue ( which seems to afflict all version up to 2.1.x, then a workaround is available here.

Fast forward then and I’m now sitting here front of SQLDeveloper 2.1.1.64.45 on Windows Vista…and wondering what exactly it’s done to all of those package bodies that were there a moment ago.
What follows is a summary of my attempts to find out just what is going on and how to get around it. Continue reading

Going Postal – Validating UK Postcode format in PL/SQL

As a database programmer, addresses will crop up from time to time with the inevitability of death and taxes. Depending on the state of the database your working on at the time, it can feel like either or, in extreme cases, both.
This post is about the joy of UK postcodes and how you can validate that they are correctly formatted with…wait for it….regular expressions.
Before you run off screaming into the woods, I’d hasten to add that this can be achieved without overdue exertion of the top row of your keyboard. Continue reading

More Nefarious Nullable Nonsense – Coalesce

OK so it’s not really nefarious but I couldn’t think of another word beginning with “N”.
You may also be relieved to know that this is the dying parp of the vuvuzela on this blog.

This is really just a bit about coalesce – a little used but extremely useful SQL function.

Let’s get the World Cup example out of the way. Here’s a table containing details of the four semi-finalists from this year’s tournament.

Conveniently enough, they consist of two previous winners, one with a best performance as beaten-finalists, and one with a previous best of 4th. Too good an opportunity to miss…. Continue reading

Passing parameters in SQL*Plus – or What’s the opposite of a Chick Flick ?

Watching Iron Man 2 the other night, I was somewhat surprised by a brief appearance on screen of someone who looked remarkably like Larry Ellison.
No, he wasn’t the villain although – depending on what Oracle ends up doing to MySQL – he could be a candidate for the role in Iron Man 3.
As a result of riding this cinematic rollercoaster, I experienced two profound revalations.
The first is that, despite it’s poor relation status in the array of tools available to the Oracle developer, SQL*Plus can still be incredibly useful ( OK, I’ve always thought this – but I’m flashing my Poetic License here and no, it hasn’t expired yet).
The second is that the opposite of a Chick Flick must be a Bloke Buster. I know, I should really just stick to the programming stuff.
Continue reading

Caching in – adventures in Oracle Tuning

Given the job of tuning a problem statement, I’ll usually try to work on it on a lightly used database. More importantly, where practical, I’ll execute the statement twice, one after the other, and use the response time for the second run as the starting point – the timing that my tuning changes to the statement need to improve upon.

My reason for doing this is simple – I want to filter out the effects of database caching on performance of this individual statement.

Obviously, this approach tends to work best for relatively short running queries. If you’re lumbered with a 12-hour Batch behemoth, then this isn’t going to be an appealing plan of attack. Continue reading