Born Before Computers – fogey foibles and forsight for the humble insert

Apparently, I am considered by some to be stuck in my ways.
For example, Deb now refuses to stand with me in the supermarket queue because of my tendency to argue with the automated checkout.

This even extends into my working life where I have a colleague who is a bit more hip and with it when it comes to writing code.
The rest of us in the Oracle team – slightly older than this person it must be said – are apparently “BBC”.
This isn’t some reference to the venerable British Broadcasting Corporation, nor even to the BBC micro which was popular back in the 80′s.
Evidently, it is something of a disadvantage to have been “Born Before Computers”.
I’ll confess, I do write most of my database code in a text editior and run it via SQL*PLUS. Whilst I use an IDE for looking at stored database code, I’m not that keen on using it as a code environment.
As is usually the case, sometimes things are done in a certain way for years because that’s the best way to do it…and sometimes it’s simply because “that’s how we’ve always done it”.
The trick is, knowing which is which.

All of which brings us to the INSERT statement.

Oracle Batch Job Logging – a framework for domestic harmony

Like most men, I have a standard of tidiness and cleanliness that I think of as “Bloke Clean”.
Deb’s standards are rather higher ( she would say normal). The difference can occasionally be a source of tension.
“Maybe I’ll just run off with that Steven Feuerstein bloke !”, she may have said during one of our discussions about the state of the study.
“Oh really ?”, I might retort, “and what’s he got that I haven’t ?”,
“Money, fame, talent and his own framework…not to mention a cleaner, I’ll bet”.
“Well…at least I have more hair”, I might say, disconcerted by her surprisingly comprehensive knowledge of someone who, it must be said, is not exactly famous outside of the wonderful world of Oracle.
“Not by much.” would probably have been the devastating reply.
Predictably, a compromise has now been reached…the upshot of which is that I’ve just spent the afternoon becoming intoxicated by the fumes from various cleaning products…and the study is now gleaming and all the papers filed away…and we’re getting a cleaner.
As for the money, fame and talent…well, I’ll just have to make do with the framework for now.
Truth be told, calling it a framework is overstating things a bit. But hey, it does give me an excuse to come up with a (possibly) amusing name.

ORA-00845: MEMORY_TARGET error installing Oracle XE on Mint and Ubuntu

It was my turn to “cook” tonight. Deb was quite emphatic on that point. Continuing the fine and long-held tradition, sustained through generations of British manhood, I duly trudged down to the chippy.
Fish and chips, with that unique and exquisite smell of malt vinegar. Never mind all those fancy aftershaves, for us Brits it’s Sarsons…pour homme.
Except that, when I get to the shop, I find that I have no cash on me and they don’t accept cards.
No, not even “Chip and Pin”.
Eventually, the hunter gatherer returns ( having made a short detour to an ATM) to be greeted by the now ravenous family. Honestly, this cooking lark is all go.

It could be worse I suppose. I mean, the recipe for Victoria Sponge doesn’t suddenly stop working for no readily apparent reason, unlike, to take a random example, installing Oracle XE on Mint and Ubuntu.

When I wrote the original post, all was working perfectly. Mint 11, Oracle XE 11g, job done.
However, Mint 13 ( or Maya, if you prefer) is a bit of a different story. So, for that matter is Ubuntu 11.10 and above.

At this point, I’d like to say a big thanks to Gil Standen, whose comment on the original post was spot on in pin-pointing and solving this issue.

So, if you’ve found your way here having been frustrated in your installation attempts by this pesky error, what follows is an explanation of the issue, together with the steps that I used to resolve it on Mint 13.

Playing with CLOBs – and a simple way of rolling back database changes

Deb finally agreed to make an honest man of me and we tied the knot last month.
This happy event coincided with the Olympics, during which I noticed my new bride taking an unusual interest in various events…or possibly the competitors.
When she did remark on the six-pack of one particularly fine specimen of male athleticism, combined with a meaningful look, I felt compelled to expound
my theory of male fitness, which can be summarised thus :
I regard my body in the same way that I regard beer – why have a six-pack when you can have a keg ?
Yes, in terms of body-shape, I must admit that mine does not so much resemble the genius of a work by Michelangelo as it does the work of a cooper last thing on a Friday before heading off to the pub. However, there is a more pressing problem occupying my mind at the moment, namely, how best to rollback a release of code to an Oracle database.

Of course, there are a number of solutions to this particular problem. If your system needs to remain available during the release, Edition based re-definition is certainly worth a look. If you have the luxury of down-time whilst doing the release, then you have the option of a Point-in-time recovery should things not quite go according to plan. For a smallish release however, that does seem a bit like overkill.

Alternatively, if you’re on Enterprise Edition ( and once again, assuming you have the database to yourself), you might even consider Flashback.

The thing about stored program units is that, unlike other code, you don’t have a source file.
CREATE OR REPLACE overwrites the code you are changing so you need to make a conscious effort to back up what you’re changing if you might need to put it back.

What I’m going to cover here is

• How to deal with Murphy’s Second Law
• Using PL/SQL to backup specific stored program units prior to a release
• Storing old versions in the database as CLOBs

But first, we need an appropriate example….

The CASE against DECODE and the Misery of Penalties

Euro2012 has come and gone. That sigh of relief is the sound of Deb reclaiming the TV remote and banning me from watching any more sport for the rest of the summer.
Spain have confirmed themselves as one of the great teams in history by winning yet again.
It has been said that they are boring. As far as I can see, the only boring thing about them is their predictability in not letting anyone else win.
By that measure, England are pretty boring as well although, if you wanted to be a bit more positive, you’d say consistent. How much of a lottery can penalties be when you lose all the time ?
I’m not even going to pretend that the above ramble connects in any way to the subject of today’s wander through the wacky world of ANSI SQL…although you may notice that I’ve taken inspiration from recent events for some of the examples.
When Oracle first came out, there was no ANSI standard SQL. There weren’t any other relational database on the market.
As a result, there are various programming constructs that are still a bit non-standard.
Yes, Oracle has introduced the ANSI standard equivalents over time and insisted that both the proprietary and ANSI syntax work in exactly the same way. This is true. For the most part.
I have already noted the advantage of ANSI join syntax when using more than one outer join.
Here, however, I’m going to turn my attention to a useful little feature of the CASE statement.

Time travel with fixed_date

My Dad is a bit of a clever-clogs at the moment.
When he confidently predicted that Chelsea would beat Barcelona in the Champions League back in April,
I put this down to an affinity with the Chelsea squad which, lets face it, is rather nearer to his age-group than those at most other big clubs.
Since then however, he’s been on a roll, culminating in his unerring prediction that Sweden would beat France in the Euros.
This almost cephalopodic accuracy ( remember Paul the Octopus) is about to be put to it’s sternest test.
According to Dad, England will beat Italy on Sunday.
Hopefully, this is just another example of his prescience rather than a case of being swept up in the mood of the moment. Roy Hodgson has gone from being “why on did they go for this old codger” to “I always knew he was the man for the job” in the space of six weeks.
English football fans, fickle? I don’t know where you get that idea.
All of which provides a fairly tenuous link to the subject at hand – testing date sensitive changes in Oracle.

Leading Zeros…the bleeding edge of TO_CHAR format masks

We’ve been busy playing with our new house.
Deb has re-arranged the furniture about three times since we moved in.
Well, she’s actually just specified where she wants the furniture, it’s me who has actually moved it about.
In fairness, she is the one who brings all the taste, style and interior design skills to our relationship. I’m more of the Igor who just does the legwork at her bidding.
My protestations about the importance of Euro 2012 have been met with the response that it can’t be that important a football tournament because Wales aren’t playing in it.
One of the benefits of moving into a new place is that stuff just works. You hit a light switch and the light comes on.
It’s a bit like using TO_CHAR to convert a number in Oracle really. Look, I’ll show you…

Running a Windows Batch file from DBMS_SCHEDULER

In an ideal world ….

Luton would have won the play-off final
I would have won the lottery by now
…and Oracle databases would run on Linux.

Out in the real world however, there are times when Oracle running on a Windows server is just unavoidable.
That’ll be the Real World with real data and real security issues, mixed in with – possibly – the real need to initiate a batch script from inside the database.
What follows are details of how to do this in Oracle 10g R2 running on a Windows server.

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 ?