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…

Anchored Declarations and the Brownie Point Economy

This week’s hot conversational topic in the Nut and Squirrel was the Global Economic Crisis, with particular reference to a little-reported side-effect that has huge ramifications. I am, of course, referring to the devaluation of Brownie Points.

Unless you’re English, I guess some explanation may be called for at this point. So, at the risk of getting all anthropological…
Brownie Points are awarded by females to their mate for certain actions. When enough brownie points have been accrued, the male of the species can have these converted into a Pass.

The Pass can be used for a night out with the lads, at the footie, or whatever other pursuit is of interest.

This system is instinctively understood by females, although, due to the sudden and unexpected fluctuations in value, less so by the males.

You will often hear conversations such as :

“We’re going to Luton Saturday, their playing Mansfield, you reckon you can get a pass ?”
“Sorry, we’re at the Garden Centre on Saturday, need to earn some brownie points.”

This system goes under many different guises but is essentially the same the world over.
Lately however, it has become apparent that males are having to work harder for their brownie points than previously. The suspicion is that this is related to the retail price of women’s shoes.

What a mess. Where can you put your hard earned savings to ensure an index-linked return ?
Well, if you really want to know, you’ll have to read the Financial Times. Variables in PL/SQL, however, can be indexed-linked to database columns by the simple expedient of an anchored declaration.

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.

DBMS_APPLICATION_INFO – Are we nearly there yet ?

Deb has come to the conclusion that, when on a long car journey, I’m not a great passenger.
“Are we nearly there yet ?” I enquire politely…usually around five minutes into a four-hour journey.
“No, not yet”, comes the patient reply.
“Are we almost nearly there yet ?”
“No, I’ll let you know when we are”, she responds with iron patience.
A few minutes pass…
“Are we almost nearly almost there ?”
At this point, I’m usually offered the option of walking the rest of the way.
It’s the same with long-running programs on the database. I want to know how far it’s gone and how far it’s got to go.
Oh, what Deb would do for an in-car equivalent of DBMS_APPLICATION_INFO.

Deb would probably have some sympathy with the DBA who gets a call from a user who has a process running and is, essentially, asking “are we nearly there yet ?”
Fortunately, with a bit of foresight and the judicious application of a little DBMS_APPLICATION_INFO magic, the answer to this question is right there in V$SESSION, or even V$SESSION_LONGOPS.

PL/SQL Injection – The Doctor Will See You Now

Following on from my recent post about SQL Injection I had an excellent comment from Gary.

Whilst we’ve been able to establish that the PL/SQL solution we implemented does not suffer the same vulnerability to injection as the concatenated SQL statement, this does lead us to a further question – does using PL/SQL automatically render us immune from injection attacks ?

For the purposes of this post, I’m going to leave PHP to one-side and concentrate on the PL/SQL side of the matter.

Oracle, bind variables and SQL Injection – Keeping out unwanted guests

My son, Michael ( yes, it is the only name I can spell), is currently following in his father’s footsteps and studying Computer Science.
As is only natural, he does occasionally have the urge to rebel against all that his parents hold dear. In his case he’s rejected the path of light and Linux and has become … a Microsoft Certified Professional. Oh the shame. Where did I go wrong ?
All of which links, if somewhat tenuously, to the subject at hand. When he took his first steps into the world of programming, we had a look at PHP ( as part of a LAMP set-up, naturally).
In one of the introductory manuals, we came across an example of how to authenticate web users against a database.
The author was clearly trying to introduce various language concepts and would certainly not claim that his example was intended for production use. However, with a bit of tweaking for use against an Oracle database, it does offer a very clear illustration one area of the potential vulnerabilities of web applications to SQL Injection attacks. It also offers the opportunity to illustrate a major benefit of using bind variables in queries against Oracle – i.e. protection against SQL Injection.

I know that a fair few people who stumble across this site are new to Oracle and want to play around with Oracle XE. These people are also usually pretty experienced in other technologies (hi Wayne, hope you’re still enjoying all that sunshine).

So, the purpose of this post is to :

• Illustrate the way in-line SQL statements can be injected
• Show how this can be countered in an Oracle database by use of bind variables
• Have a look at letting Oracle handle user authentication
• Celebrate the visionary genius of Messrs Young, Young and Johnson. “For Those About to Rock” was not merely an album of raucous Blues-based Heavy Metal, but a prophecy about the potential pitfalls of Web Application development.
• Oh, and give you the chance to laugh at my PHP prowess ( or lack thereof)

Database Design – Denormalization, Codd and the Copa America

Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America.

More Oracle Dating Tips – High Days and Holidays

Following on from my post about Dates the other week, I’ve been looking around (afer all, there’s no harm in looking). There’s a fair number of clever date manipulation routines out there, calculating a business week, the tax year etc.
Work, work, work. What would be really useful is something that can work out when the Public Holidays are this year.

Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.
At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here.

Sinister Synonyms and Dependencies in Oracle

It’s been quite an eventful week. Deb has got her results back and is now officially a lady of “Distinction” (two of them, no less). Even Wales’ narrow defeat to England in the Rugby hasn’t put a dent in her good mood.
I, on the other hand, found myself doing my Marvin-the-paranoid-android-as-a-DBA impression the other day….”Synonyms. Loathe them or hate them, you can’t ignore them”.
Now, whilst synonyms definitely have their uses, they can be something of a double-edged sword.
The cause of this particular downbeat assessment of their merits was the fact that I’d deployed my CRUD tool on a new database, but it had failed to pick up some dependencies.
Let’s have a closer look at this issue and see how Oracle’s own DBMS_UTILITY copes with these circumstances.