So, you’ve noticed that SQLDeveloper ( any version prior to 2.1.1) doesn’t show triggers on views. At the same time SQLDeveloper 2.1.1 doesn’t show package bodies unless you have a very highly privileged account.
If you’re determined to press-on with 2.1.1, you can see a workaround for the package body problem here.
If however, you’d prefer to wait until they’ve ironed out this particular kink before taking the plunge, but want a solution to the invisible triggers, read on … Continue reading →
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 18.104.22.168.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. Continue reading →
As a database programmer, addresses will crop up from time to time with the inevitability of death and taxes. Depending on the state of the database your working on at the time, it can feel like either or, in extreme cases, both.
This post is about the joy of UK postcodes and how you can validate that they are correctly formatted with…wait for it….regular expressions.
Before you run off screaming into the woods, I’d hasten to add that this can be achieved without overdue exertion of the top row of your keyboard. Continue reading →
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. Continue reading →
Watching Iron Man 2 the other night, I was somewhat surprised by a brief appearance on screen of someone who looked remarkably like Larry Ellison.
No, he wasn’t the villain although – depending on what Oracle ends up doing to MySQL – he could be a candidate for the role in Iron Man 3.
As a result of riding this cinematic rollercoaster, I experienced two profound revalations.
The first is that, despite it’s poor relation status in the array of tools available to the Oracle developer, SQL*Plus can still be incredibly useful ( OK, I’ve always thought this – but I’m flashing my Poetic License here and no, it hasn’t expired yet).
The second is that the opposite of a Chick Flick must be a Bloke Buster. I know, I should really just stick to the programming stuff. Continue reading →
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. Continue reading →