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.

Triggers are bad m’kay – more Mutant Madness

The title of this post should be read in the style of Mr Mackey from South Park.
Just to be clear at this point – I do not subscribe to this view. Triggers, like most things Oracle, have their place. It’s knowing where and when to use them that’s the trick.
In a recent post, I outlined the solution to the Mutating Table issue that is offered by Compound Triggers.
Unfortunately, despite watching all of the X-Men movies, I failed to realise that Mutants tend to be quite resilient.
Joaquin pointed out that this trigger would not work as expected when a statement contained multiple updates.

Caching in – adventures in Oracle Tuning

Given the job of tuning a problem statement, I’ll usually try to work on it on a lightly used database. More importantly, where practical, I’ll execute the statement twice, one after the other, and use the response time for the second run as the starting point – the timing that my tuning changes to the statement need to improve upon.

My reason for doing this is simple – I want to filter out the effects of database caching on performance of this individual statement.

Obviously, this approach tends to work best for relatively short running queries. If you’re lumbered with a 12-hour Batch behemoth, then this isn’t going to be an appealing plan of attack.

Compound Triggers – Managing the Mutant Menace

Now I’ve got 11g up and running, I’ve finally had the chance to mess about with some of the new features.
Anyone who has done any amount of work with Oracle triggers will be familiar with the dreaded ORA-04091 mutating table error.
You’ll be relieved to hear that I’ve managed to resist the temptation to start talking about X-Men. The medication must be working.

Instead, here’s a quick demo of an 11g way of getting around this particular problem.

PL/SQL Syntax Highlighting in Textpad – for when you can’t play with Penguins

When I’m mucking around at home, it’s Linux all the way. Unfortunately, at work ( in desktop terms, at least) I’m still stuck with being a Microsofty.

This leaves me with Textpad as my program editor of choice.

As promised then, here’s the quick and dirty guide to enabling syntax highlighting for PL/SQL in Textpad ( works for versions 4 and 5) …

Self-Inflicted SQL Injection – don’t quote me !

After my recent post about escaping quotes in SQL scripts, I was surprised and delighted to receive a mail from Alexander Kornbrust, CEO of Red Database Security.
In it, he said he’d read the post and pointed out that the code therin was vulnerable to SQL-Injection.
I was fortunate enough to work with Alex, before he went off to become famous, so I know that he’s a bona fide expert in all things Oracle, especially security. Even so, I was initially puzzled by his assertion.
After all, the code I’d posted was an example where the code is held in a script and NOT in the database so wouldn’t be vulnerable to being executed by someone who’d hacked into the database itself. After all, SQL Injection happens interactively doesn’t it ? The hacker needs to be probing for weaknesses via a web front-end or similar. Don’t they have to be physically typing stuff in somewhere for this to work ? Er….apparently not.
Having asked around a number of Oracle developers, this would seem to be a widely held misconception. In terms of SQL Injection attacks, we’re all familiar with the classic HTML login form which POSTS to some mid-tier script or program which in turn, simply concatenates the username and password strings supplied by the user into a query then fires it off unthinkingly at the database. But how can you be attacked when the attacker isn’t even around at the time ?

Return in a PL/SQL Procedure – Just one more thing Mum never told me

Functions return values. Procedures pass out parameters. That’s the way it is, that’s the way it’s always been. Or that’s what I thought up until recently.
Turns out that procedures can return too. I don’t know why I hadn’t realised this before. Maybe I’m just too set in my ways, or maybe it’s not just me.
Yes, I hear you cry, but RETURN in a procedure won’t pass back a value, so why would it be useful ? Time for another trivial example…

Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit.

Reverse-Engineering a CRUD Matrix in Oracle

I think that title may need some explaining, especially if, like me, you spend your days trying to keep your database free of crud.
CRUD stands for Create Read Update and Delete. A CRUD Matrix contains details of which programs interact with which tables and in what way.
In the context of an application running on Oracle, it’s useful to know which stored program units interact with which tables for a whole host of reasons.
These mainly resolve themselves down to being able to analyse the impact of any structural changes you may want to make to your database such as changing a table definition or adding an index.

Incidentally, being such a useful concept, CRUD inevitably has many synonyms, ranging from the brutally frank ( Create Retrieve Alter Purge) to the positively hallucinogenic ( Add Change Inquire Delete) so I’m sure you can find an acronym suitable for whatever kind of day you happen to be having.

Too many declarations of “+” match this call – When Compilers lie

A compiler is often your best friend as a programmer. It will helpfully point out the fact that you may have made a typo, or are about to do something silly. Most of the time, it’ll even helpfully point out where exactly you’ve not quite got it spot on.
But, like any long-term relationship, the Programmer and the Compiler do have the occasional falling out. This happens especially when the compiler becomes recalcitrant, uncommunicative, cryptic, or just flat out lies to you.

Being primarily a PL/SQL programmer my particular relationship is with the ADA pre-compiler that lives inside Oracle. Her name is DIANA.