PLS-00364 – And you never buy me flowers !

Nestled deep in the heart of the Oracle RDBMS lies DIANA – the ADA pre-compiler which gives all your PL/SQL the once-over before sending it out into the world.
As I’ve mentioned before, DIANA can be a capricious girl, and if you upset her, she’s likely to complain about all sorts of things, some of them entirely spurious.
One such error that she tends to throw out is “PLS-00364 : loop index variable [ some cursor record variable ] use is invalid”. Continue reading

UTL_FILE in PL/SQL – I/O, I/O, it’s off to work we go

Back in the mists of time, when Broadband was a way of describing a group of fat blokes with guitars, PL/SQL blinked it’s way into the world. It’s purpose was ( and largely remains) to provide the facility to apply 3GL program structures to SQL from within the database ( hence – Procedural Language / SQL).
As an integral part of the Oracle RDBMS, most PL/SQL I/O activities are on database tables. The ability to read and write OS files didn’t arrive until much later.
Meanwhile, back in the present, things are somewhat better on the File Handling front. So, if you just have to generate that flat-file and would rather not muck about with a pre-compiler (or a Java Stored Procedure), PL/SQL will do the job. Continue reading

Getting output from Ref Cursors in PL/SQL

A colleague of mine (Martin, you know who you are), remarked the other week that he wasn’t overly interested in the contents of the blogosphere. He said that it usually put him in mind of the cartoon of the tag-cloud consisting solely of the word “me”. This got me to thinking, why do I do this ?
Let’s put my ego to one side for a moment ( pause to sounds of straining, followed by a dull thud). That was heavier than it looked.

One of the reasons for maintaining this blog is that I’ve got a quick reference to look at if I come across something I did a while ago and need a quick reminder of syntax etc. Also, my Mum likes to know what I’m up to.
The starting point for this entry was to attempt to drag together all the basic bits about Ref Cursors in PL/SQL – specifically, accessing them from within PL/SQL itself.

Whilst I was writing this, it was pointed out to me that SQLDeveloper doesn’t handle Ref Cursors quite as nicely as Toad. The specific issue was the difficulty in dumping the results into a grid, from whence it can be transferred to Open Office Spreadsheet ( or Excel).

For the most part, Ref Cursors are used to transfer data from the database to a web application. So, why would you need to start fiddling about with getting results back in PL/SQL ?
There are probably several answers to this question. However, for me, it’s mainly a case of having to trace problems raised in various support calls. Knowing what data results from each of the calls in a process usually helps a bit. 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

Taking the bind out of mid-tier queries

At least it wasn’t penalties. Another straw to clutch at – to paraphrase George W Bush – the Germans have no word for schadenfreude.
On top of that, I was in Sainsburys the day after the game and picked up an England branded top for a mere £4. The good news just keeps on coming.
For anyone not sharing in the English mood of …not so much mourning as a sort of resigned cynicism… this is still a post about Oracle stuff…but with a football theme. Continue reading

Triggers are bad m’kay – more Mutant Madness

The title of this post should be read in the style of Mr Mackey from South Park.
Just to be clear at this point – I do not subscribe to this view. Triggers, like most things Oracle, have their place. It’s knowing where and when to use them that’s the trick.
In a recent post, I outlined the solution to the Mutating Table issue that is offered by Compound Triggers.
Unfortunately, despite watching all of the X-Men movies, I failed to realise that Mutants tend to be quite resilient.
Joaquin pointed out that this trigger would not work as expected when a statement contained multiple updates. 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

Compound Triggers – Managing the Mutant Menace

Now I’ve got 11g up and running, I’ve finally had the chance to mess about with some of the new features.
Anyone who has done any amount of work with Oracle triggers will be familiar with the dreaded ORA-04091 mutating table error.
You’ll be relieved to hear that I’ve managed to resist the temptation to start talking about X-Men. The medication must be working.

Instead, here’s a quick demo of an 11g way of getting around this particular problem. Continue reading