Select For Update – Picking the lock

As a medical professional, my girlfriend is always giving me advice and tips ( apart from “that washing up won’t do itself, you know!”). You may be interested to know that Nurse Debbie’s top tips for curing insomnia are :

  1. A healthy helping of wine ( strictly medicinal, you understand)
  2. Lisen to me talk about programming

Now she’s snoring ( albeit in a ladylike fashion)…
Explicitly locking rows in application code has always been regarded as being a bit of a no-no. Let Oracle handle locking, the argument goes, or you’ll be up to your ankles in deadlocks…head-first.
Most of the time, this holds true. Sure, there’s the odd batch job daemon where you’ll lock a row in a table just to show it’s running and so shouldn’t run again before the previous iteration has completed, and maybe you’ve got a Forms block based on a Ref Cursor which requires you to lock the target table before doing any DML. For the most part however, this practice is something you want to avoid. And yet …

There are times when you just have to bite the bullet and lock that row. But when exactly does the row get locked and when does it get released ? Continue reading

PL/SQL – A Programmer’s Introduction – or Welcome to the Dark Side

This week, the Open Source Karma has been cast-aside. We’re going proprietary in a big way. We’re going to the very heart of Oracle’s power, deep inside the RDBMS – yes – it’s PL/SQL.

This post is dedicated to ( and essentially co-written by) Simon. Yes, my long-time best mate, long-time Luton Town fan, long-time Teradata expert and long time everything really ( we’ll he’s not as young as he was).
After all these years, Simon has become a bit curious about this PL/SQL thing I’m always going on about and would like to know more.
It is this desire – and large amounts of beer – that has persuaded him to play the Igor to my mad scientist and have a wander through this very quick guide to the language at the heart of most Oracle applications. In fact we came up with several possible descriptions of Simon’s role in this post, but he had a “hunch” that this was the right one.
So for him, and any other programmers who want to get up and running with PL/SQL, but don’t need to be told what a variable is, what follows is – not so much a PL/SQL 101 – as a PL/SQL 23-and-a-bit. Continue reading

The PL/SQL Associative Array – the path to untold riches

Having given the matter some thought, I’ve concluded that there are two ways to fame and fortune.
The first of these is talent. For the benefit of my Colombian readership ( hello German) :
I can’t play football like Faustinio Asprilla; I can’t drive as fast as Juan Pablo Montoya; and as for Carlos Valdarama’s hair…well mine deserted me some time ago. I do have something in common with Shakira – my hips don’t lie. Unfortunately, what they say is “this waistline is the result of too many nights in the pub”.

The second way is winning the lottery. OK, so the fame thing is a bit tenuous, but from the outside looking in, I’d say it was overrated. So, never mind the fame, quiero solo mucho dinero ( I just want loads of cash) !
Continue reading

PL/SQL Arrays – The Autumn Collection

I’ve spent some time recently playing with PL/SQL arrays in the context of uploading from flat-files.
In the course of this, it struck me that PL/SQL arrays come in a variety of shapes and sizes ( or in this case, small, medium and large).
So, if Sir – or Madam – would care to step into the fitting room, we’ll see if we can find something to suit.
Continue reading

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 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