Cumulative Total in Oracle SQL – Cricket, but not as we know it

If you spend you working life playing with databases, sooner or later, you’ll come across a situation where you want to have a running total of something or other ( usually money) included in the result set of a query.
For many years, this required quite a bit of messing about. Such techniques a covered nicely in this article by Kevin Meade.

Also mentioned by Kevin, and what I’m covering here, is the method of achieving this without too much fuss, using Oracle’s Analytic Functions. Yes, the syntax is a bit arcane on the face of it, but bear with me here.

Virtual Columns in Oracle 11g – Ashes to Ashes

The test series between the two pre-eminent nations in world cricket has finished. But never mind India and South Africa, England have just won the Ashes. In Australia. 3–1. Look, it’s not gloating. The last time England won in Australia was almost a quarter of a century ago. As a nation, the English have learned to savour sporting success as we never know when (if) it will come around again.
By happy chance, Cricket batting and bowling stats lend themselves quite neatly to being used as an example of Virtual Columns in Oracle 11.
So, at the risk of alienating any Australians reading ( and reducing my chances of getting served in a bar anywhere in the Greater London Area)…

Configuring OPAL on Ubuntu Desktop – without the Oracle Instant Client

Many years ago, my son had more-or-less worked out that Santa was a myth, but hadn’t wanted to say anything for fear of decreasing the number of Christmas presents he might get.
Taking my parental duties as seriously as I do, I took him to one side and explained the truth…
After Return of the Jedi, Darth Vader fell upon hard-times. There weren’t many film roles about for Dark Lords of the Sith. Eventually he decided upon a change of career and bought the round off Father Christmas, who was retiring.
Obviously, Darth Vader has a rather more direct approach to naughty children and if my son didn’t behave himself, not only would he not get any presents but he might get something cut off.
It is for this reason that Simon has the Darth Vader theme as the ringtone on his phone for when I call.

All of which has at best, a tenuous link to the theme of this post ( but I thought it was time to get into the festive spirit).

Following on from last week’s introduction to PL/SQL, some people have asked about using PL/SQL a web application (without all that mucky APEX stuff). In order to start working up some examples of this, I thought it would be a good idea to use PHP as a front-end. Yes – Oracle’s version of a LAMP system – Oracle, PHP, Apache, Linux (OPAL). After all, how hard could it be ?

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.

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.

DMBS_OUTPUT Can’t Count – Or When is 32767 not 32767 ?

The answer is – when it’s 255 or, to put it another way, when 9 = 10.
I’d better explain that a bit before you begin to wonder if I really should learn to use a calculator ( or even take my shoes and socks off to count past 10).

Definitely the final post about CASE – things that make you go Doh !

I’d like to start this large slice of humble pie by saying a big thanks to Glenn and Martin who both pointed out – ever so politely – that I’ve been a bit thick.

Basket Case – CASE statement in SQL

Following on from my previous post, I’m rapidly reaching the conclusion that the CASE statement is, well, a little bit touchy.
First off, here’s a quick recap or you can have a look here.

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

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.