Council Tax and calling functions from SQL in 11g

There is a frisson of excitement in our household at the moment. It’s not simply because the mighty Hammers on their way to Wembley. Oh no. It’s not even that Luton will be making the same journey with the same aim (i.e. promotion).
In fact, it has little or nothing to do with the end-of-season fun and games in the football world. The truth is, that we are finally moving from sunny Milton Keynes to …er… somewhere else in sunny Milton Keynes.
To mark this momentous occasion, Deb has busied herself with organizing the packing, working out where all the furniture is going in our new house and eyeing a whole range of utensils that will go with our new kitchen.
For my part, I’ve written a PL/SQL routine to apportion Council Tax Liability.
It should be noted that Deb has now applied to change her name to “The Long Suffering Deb” by deed pole.
Apart from exploring the algorithm required to apportion Council Tax ( for those outside the UK, it’s a sort of local tax on all domestic properties), I’ll also have a look at how 11g now allows at least three different ways of calling a database function from SQL.

Just because you’re Paranoid…

…it doesn’t mean they’re not all out to get you !
No, this isn’t sage advice for the new manager of the England Football Team ( although Mr Hodgson will surely come to appreciate it’s wisdom in the very near future), but rather something to be mindful of when writing database code.

In my experience, there can be a worrying complacency among database developers when it comes to Security.
It’s as if they feel that their code is invulnerable, protected by that firewall thingy and inaccessible to those unsavoury types who want to crack their system and uncover the goodies therein.

Sometimes, it gets to the point where I begin to wonder, is it just me who worries about this sort of thing ? Am I just being a bit too paranoid ?

Implicit Cursors are from Venus, Explicit Cursors are from Mars

Domestic bliss. There’s nothing like it. There’s certainly nothing like it in our house, particularly when I indulge in one of my endearing little foibles that is guaranteed to get Deb pouting like Angelina Jolie suffering a nasty reaction to a wasp-sting.
Whether it’s leaving the toilet seat up, or hanging my clothes up on the nearest floor, there are some days where I just can’t seem to do anything right.
Having said that, I must confess that I myself, am not a model of toleration. My own personal pout trigger is a query that looks something like this :

SELECT NVL(COUNT(*), 0)
FROM some_table;

I’ve seen this quite a bit recently, usually in the form of an explicit cursor.
Once I’ve got the rant about this out of my system, I’ll then look at how you might make single-row sub-queries a bit more efficient without ending up knee-deep in implicit cursors.
I’ll also ponder what it actually is that we really know about cursors.

V$SGA_TARGET_ADVICE – The Need for Speed

My brother Steve lives near Brands Hatch in Kent. This is quite appropriate really. He’s always been a bit of a thrill-seeker. After his motorbike accident a few years back, he seems to have decided to treat the wheelchair as merely a transfer from two wheels to four.

“I’ve got a new Nissan” he told me the other day. “It’ll probably go round Brands Hatch quite slowly”.

What’s this ? Has the daredevil spirit suddenly disappeared and been replaced by a Nissan Micra ?

Er…No.

Not a Nissan Micra

Steve’s latest mode of transport is a Nissan GT-R. It looks like it’s breaking the speed limit, even when it’s parked on the drive.
Steve assures me that hitting the throttle is not so much a means of increasing speed as a command to engage warp drive.

All of which speed-freakery brings me to the point of this post, namely sizing the SGA_TARGET so that your database will be just that bit faster.

APEX – Getting back to where you came from

Ah, sunny Milton Keynes. There’s no place like it. Nestling in the heart of England, halfway between London and Birmingham, my home town has plenty to recommend it.
Yes, many of my countrymen like to poke fun at the Concrete Cows that are Milton Keynes’ most famous landmark. However, the one irrefutable benefit of living here, especially if you tend toward the geeky, is that Milton Keynes is also the home of the National Museum of Computing, hosted in Bletchley Park.
One thing about Milton Keynes is the interminable roundabouts throughout the city. Just keep turning left at every roundabout and you’ll be guaranteed to end up back where you started.

The same however, cannot be said of APEX – at least, not without a little bit of work.

In the example that follows, we have a page in an APEX application that can be invoked from a number other pages. The target page has a back button to return to the page you just came from. The question is, how do you make the target page re-direct back to the correct calling page ?
It must be said that the solution that follows can best be described as crude but effective. I’d be interested to hear if you come up with a more elegant solution.
Anyway, here goes….

Getting APEX to play with Ref Cursors

It’s that time of year again. Things are a bit tense around the house.
The other morning, I woke up to find that someone had placed a leek in my slippers.
Yes it’s Six Nations time again. England are playing Wales on Saturday. The lovely Debbie is getting into the spirit of the occasion…by exhibiting extreme antagonism to all things English.

Whilst the patriot in me would like to cheer on the Red Rose on Saturday, I have decided that discretion ( or in this case, cowardice) is the better part of valour and will instead, sit quietly in the corner, hoping for a draw. That way, I’ve not sold out completely and next week will be far more pleasant if Wales have not lost.

For those readers who know Rugby Union as merely another one of those odd games that we English let our former colonies win at, all you need to know is, the Welsh take this sport very seriously.

In the meantime, I’m trying to keep a low profile, which means playing around with APEX 4.1.

The heady excitement of discovering the first decent GUI development environment for PL/SQL programmers since Oracle Forms is now starting to be replaced by some of the harsh realities of modern web development.
For example, how can I reuse all those terribly useful functions that return Ref Cursors ?
I mean, they work fine in PHP and various other languages, and APEX itself is written in PL/SQL. Should be easy, shouldn’t it ?

Er, no.

APEX simply refuses to play. “I laugh in the face of your weakly typed Ref Cursor” it seems to say. Clearly, some persuasion is required if I’m not to end up with a lot of code locked away in my APEX application, unusable by any other programming language I might want to use to build a web front-end for my database.
The way to an APEX application’s heart is, as will become apparent, through Pipelined functions.

Debbie in Linuxland

For those of us who use Linux on the Desktop, it’s probably fair to say that we live in interesting times. Having sat on the fence that is Gnome 2, whilst looking on as the relative merits of Gnome Shell, Unity, KDE and XFCE are hotly debated, I was recently given a fresh perspective on this particular debate by the lovely Debbie.

What follows is the story of how Deb converted to Linux, told (for reasons which will become apparent) through the medium of fairytale.

VPN access on Mint using rdesktop

This post was going to begin with one of my occasional bulletins on the fortunes of Luton Town, beloved club of my mate, Simon. However, the man himself has been bitten by the blogging bug and the resultant musings on all things Teradata ( and various other topics) can be seen here.
So instead, I’d invite you to consider the Nordic majesty that is Milford Sound. Nestled in the heart of Fjordland in New Zealand’s South Island, this watery expanse evokes awe and wonder, even in a land where jaw-dropping scenery is always just around the next bend.
Milford Sound was itself named after the equally picturesque sounding Milford Haven. Yes, Milford Haven in Wales, site of one of the largest oil terminals in Europe.
I should note at this point that the Welsh Ambassador has demanded that I point out that Milford Haven does have some nice bits.

Moving swiftly on, in the vain hope of avoiding domestic disharmony, my point is, the fact that two things share common characteristics doesn’t mean that they are necessarily identical.

All of which provides a somewhat tortuous link to the subject of this post, namely, setting up remote desktop access via a VPN on Mint.

Now, you’d think this was pretty much the same as on Ubuntu, and it is…up to a point.

Turning off Code Completion in SQLDeveloper – a Grumpy Old Man Fights Back

“You’re problem is you’re BBC”, a colleague remarked recently.
On further investigation, I found this not to be some reference to the British Broadcasting Corporation but rather that I had been “Born Before Computers”. Yes, I grew up – in computing terms at least – on the Command Line.
A number of things have changed since those dim and distant days.
Recently, I had a comment from Darryl claiming that chocolate bars had also been gradually reducing in size.
Damning photographic evidence of this scandal has recently come into my possession…

A Bounty Bar from 1980 together with it's modern counterpart

There’s more. My phone keeps insisting that I can’t spell and arbitrarily replacing words when I’m writing an SMS.
“You’re just the breast” took a bit of explaining to a rather skeptical Deb.
Having finally persuaded my phone that I really don’t require it’s assistance when composing a short missive, I have now turned my attention to SQLDeveloper.

Nested Tables – Flat-packed data in an Oracle Table

In the aftermath of the holiday season, there follows the inevitable January sales.
This year, I have been spared the inevitable trudge around the stores. Deb has hurt her knee and has therefore been restricted to browsing on-line.

I thought she “kneeded” cheering up, but to date, my attempts at lightening the mood, seem only to have given her the “kneedle”.

Sitting quietly, whilst Deb is wandering through various furniture store websites, I had cause to reflect on Oracle’s own version of Nested Tables.
These were introduced way back in Oracle 8, when Oracle confidently predicted that the Object-Relational Database was the way of the future.
Imagine if they were just bringing this feature out now. You can picture it. Larry would have spent months making disparaging remarks about IKEA’s occasional table range, before unveiling his own version, which was better, cheaper and more efficient.

Whilst you’re never going to be able to rest your pint on one, a Nested Table in Oracle may be useful on occasion.