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. Continue reading

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. Continue reading

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. Continue reading

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. Continue reading

Speed Dating – NLS_DATE FORMAT in Oracle

When dealing with dates in a program, I’ll tend to err on the side of caution and explicitly do the conversion from a varchar to a date, specifying the format so there’s no chance of anything unexpected happening if the program should run in a session with a different NLS_DATE_FORMAT from that I’m using.
However, if for example, I need to do a one-off data fix, sometimes, I just can’t be bothered with all that typing.
Continue reading

Help – DBMS_SCHEDULER keeps Spamming me…and can’t tell the time either

Sundays – a day of rest. Certainly true for me. Sunday morning is a time for lazing around leafing through the colour supplements and thinking about nothing in particular. Sunday 23rd October was a little bit different.
Wide-awake at 8 am ( I didn’t know that there was such a time as 8am on a Sunday), like several million others, I was wondering what would confront the All Blacks – the Gallic flair with which France had swept aside England or the Gallic shrug with which they had surrendered to Tonga ?
Look, I’m not really a New Zealander. Yes, I was born in Auckland but both my parents are English and I’ve lived most of my life in England. However, like anyone with a connection to the Land of the Long White Cloud, there is a part of my soul, however small, that takes the form of a Rugby ball.
At the end of the match, I was able to join my “fellow” Kiwis in, not so much paroxysms of joy as a huge collective sigh of relief.

On the whole though, I’d rather not have to see Sunday morning from that early on. So, if there is, for example, something that needs to run on my database on a Sunday morning, I’d rather the database just did it without my intervention.

What I plan to do here is :

  1. set up a scheduler job
  2. explore the ways in which we can control whether a class of job runs on a given database
  3. stop jobs running on database startup
  4. teach the scheduler how to tell the time – especially in terms of daylight saving

Continue reading

Oracle SQL and PL/SQL Coding Standards – Cat Herding for Dummies

Whilst in Montreal recently, Deb and I made a pilgrimage to the Circuit Giles Villeneuve, home of the Canadian Grand Prix. When not in use, the track is open to the public. It’s divided into two lanes – one for people to walk and cycle down down, and a one for people to drive down.
You can just imagine flying round in an F1 car. You come out of the excruciatingly slow L’epingle hairpin and build up to top speed as you tear down the Casino Straight. Ahead lies the final chicane before the start/finish line. A tricky right left combination with the treacherous curb on the inside of the last turn ready to spit the unwary into the Wall of Champions on the opposite side of the track.
At over 300 kph you start to think about spotting your braking point. Suddenly, this comes into view….

What do you think this is, a race track ?

… and now you know what it’s like to be a programmer, who has channeled raw inspiration through his or her dancing fingers to produce a thing of beauty and elegance…only to run into the QA person pointing out that the commas are in the wrong place according to page 823, paragraph 2 sub-section e of The Coding Standards.

Often measured in weight rather than the number of pages, Coding Standards documents are often outdated, arbitrary and just plain wrong.
On the other hand, their absence can cause much heartache, not least to those poor souls in support who are trying to maintain code where the Agilista philosophy of Code over Documentation has been taken to the ultimate extreme.

What follows is an attempt to make sense of the Coding Standards conundrum.
I’ll look at what I think a Coding Standards document should contain, and what it shouldn’t.
Then I’ll give some suggestions as to standards for Oracle SQL and PL/SQL which you can either embrace or throw rocks at, depending on your preference.
Before all of that however, I feel the need for some serious catharsis… Continue reading

ROWNUM, Random updates and the Alert Log – A trip down Memory Lane

I met up with an old friend recently. Deb and I were in Toronto, hometown of a certain Simon Jennings.
Apart from being a top bloke, Simon was also my first mentor in the mysterious ways of SQL, a sort of Obi-Wan Kenobi to my Anakin Skywalker, but without the light-sabers.

Ah those heady days when the world was young…and I first discovered ROWNUM.
That pseudo column was one I used often when investigating the structure and data within tables. It was also, the source of some confusion.
Continue reading

Oracle External Tables or What I did on my Holidays

This week’s missive is coming to you from the netbook. Deb and I have pushed the boat out this year and we’re currently in Canada for our holiday.
This has nothing at all to do with Oracle External Tables, but does explain the flavour of the examples that follow. Continue reading

If you can’t do it in PL/SQL, do it in SQL

The tension was palpable on the bridge of The Enterprise. The hulk of the giant Teredation Cruiser filled the View Screen.

With baited breath they awaited the response of the Teredation Captain to the message they had just transmitted.

Suddenly the image on the screen changed from that of the menacing warship to Simon, Lord High Hatter of the Teredations. In a voice which hinted at an unaccustomed uncertainty, he exclaimed,
“What is this PL/SQL of which you speak ?”

OK, so maybe it wasn’t the bridge of the Enterprise so much as in the beer garden at the Nut and Squirrel. The question, however, is pretty much accurate.

In an attempt to distract himself from the sad news that Claude Gnapka had finally left Luton for Walsall, Simon posed the following programming problem :

He needed a SQL query to return the first working day on or after the 23rd of the month, together with the first working day of the following month. He wanted both dates to be returned in the same row.
The catch ? Simon works on Teradata which, owing to a glitch in the Universal Translator ( or something), doesn’t have anything like PL/SQL or T-SQL built in. Continue reading