Generating an md5sum on Oracle Database LOBs – or how to organise your Holiday Snaps

Following the trend in these straightened times, Deb and I decided to stay at home this year rather than going away on holiday.
I say “decided”, but this was really more due to the fact we were terribly grown up and bought a house last year.
As a result, the only recreation we could afford was a walk around the garden…whilst pushing a lawn-mower.
In an attempt to recall happier times, I’ve had a look back of some of the photos from our last proper holiday, in Canada.
As well as providing some happy memories, this also gives me the opportunity to explore how to compare an operating system file ( such as a jpeg) with a LOB held in the database.
Continue reading

How Oracle uses Space, sort of.

Space. The Final Frontier.
My long-suffering Mrs does enjoy a bit of sci-fi especially if some hunky all-action type is wandering around with his shirt off.
“That man has such a nice personality”, she may well sigh, staring dreamily at the screen.

As with any software Oracle error messages can look as if they’ve been put together in some alien language.
This is especially true if your fairly new to Oracle.
When you get space errors in Oracle, the answer is not necessarily to simply add more space.

What we’re going to look at here is :

  • what a tablespace is and the various things they are used for
  • how redo logs work ( and how they are archived)
  • some of the space related errors you may encounter and what the underlying causes may be

Of necessity, I’ve made some generalisations here. The purpose of this post is not to provide an in-depth technical guide to the inner workings of Oracle. Rather it is to provide enough information for you to work out whether you should be looking up the phone number for your hard-pressed DBA, or looking at that bit of code you’ve just run.

Also, like the author, this post is a bit short of cache. For the sake of simplicity (and that weak pun), I’m going to pretend that Oracle uses memory in one amorphous lump.
Additionally, I’ve not taken into consideration Direct Path Inserts.

Continue reading

Re-structuring data for Hierarchical Queries – or (Tree) Walking With Big Macs

Steve McNulty. Even the name sounds a bit hard. This is not the hero in Jason Statham’s latest celluloid exploit. Neither is it the central character in a hard-bitten cop drama.
Steve McNulty is, in fact the current Luton Town captain and a member of an endangered species – the stopper Centre-Half.
When you first set eyes on him, he looks, well, a bit chunky. You might imagine his nickname to be “Big Mac” because of his penchant for a certain fast-food chain.
This is something of a mis-conception.
Firstly, he’s not overweight. In contrast to the other players on the pitch, his body has not so much been honed to athletic perfection as hewn from solid rock.
It is a build that has not been seen for years in the elite (effete ?) Premier League.
He’s not the fastest player, as you’d expect, but he’s strong in the tackle. When he heads the ball, adjectives such as cushioning and glancing do not apply. It’s a Kirby Kiss (he’s a Scouser). The ball is definitely not his friend.
So, Big Mac he is not. He couldn’t be associated with anything that’s served with namby-pamby french-fries. A McNulty burger is a huge slab of meat wedged between two halves of a cottage loaf. It would only ever be served with chunky chips.
It’s McNulty and friends that provide the inspiration for the examples that follow.
I recently came across a situation where I needed to take some relational data and convert it into a hierarchy for the purposes of dropping it into an APEX tree. This proved slightly more challenging than I originally thought. Continue reading

Native Dynamic SQL – Dodgy Code and DRS

Dynamic. That’s a positive word if ever there was one. Ascribing this adjective to anything would convey an image of energy and forward momentum.
On the question of Dynamic SQL, the images are rather more equivocal.
On the plus side, Native Dynamic SQL gives you the ability to :

  • execute DDL statements from within PL/SQL programs
  • code for instances where the required DML statement is not known ahead of time

On the flip side, it can also mean code that is:

  • difficult to read and maintain
  • prone to performance problems
  • insecure

To wander through this minefield, I have enlisted the support of a world where DRS does not stand for Dodgy Review System.
Yes, it’s the wacky and entertaining (not always intentionally so) world of Formula 1… Continue reading

Turning off Password expiration on Oracle XE and Apex

It is a strange time in the UK.
I’m not referring to England being 2-0 up after 2 tests in the Ashes (something that happens about as often as a Briton winnng the Men’s Singles at Wimbledon), nor the fact that a Briton has won the Tour de France for the second year running.
Stranger even than that is the bright yellow ball in the sky which has replaced the traditional warm rain of the British Summer.

This phenomenon has had a strange effect on the cat. Her animal instincts obviously alerted by the unfamiliar change in the climate, she currently spends almost all of her time out in the garden.
Unfortunately, she seems to have decided to treat said garden as something of a litter tray. This will necessitate something of a mine-sweeping exercise before I next mow the lawn.

It’s frustrating when you’re anxious to start something but then hit an unpleasant roadblock.
For example, you may have decided to have a play with that Oracle XE/Apex installation on your laptop that you haven’t used for a little while. However, when you come to connect, you realise that you can’t remember the password.

Health Warning – Before I go any further, I should point out that doing this is not something I’d recommend on anything other than a system that’s being used simply as a playground for you to experiment and which contains no sensitive data.

Continue reading

Simple Pleasures…rlwrap and SQL*Plus Command Line editing on Linux

Fat, bald, likes a drink and a smoke. No, not me. That’s a description of Darren Lehman, the new coach of the Australian Cricket Team.
Sounds like a good bloke to me.
As a cricket fan, with the Ashes as the highlight of the sporting summer, I’m getting a horrible sense of deja vu.
If you read the press, Australian and English, you might be forgiven for thinking that the series is a foregone conclusion.
Yes, England should win, on paper. However, unless the groundsmen at the relevant venues have been doing something very innovative, the Tests themselves will be played on grass.
In order to take my mind off some of the more worrying parallels with this series and the one in 1989 – when Alan Border and a bunch of Aussie no-hopers demolished England 4-0 – I’ve been looking at one of those niggling little problems that I always mean to get sorted but never quite get round to.

Generally speaking, I much prefer Linux to Windows. There is however, on area where Windows has the upper hand.
When you’re working in SQL*Plus, Windows allows command line recall and editing by default. This feature is not present in Linux by default.
However, Linux, being Linux, there is a handy utility that can implement this functionality. It’s called rlwrap.
What I’m going to cover here is :

  • a recap of built-in SQL*Plus editing capabilities
  • Using rlwrap with SQL*Plus
  • The joys of TAB-Completion

Continue reading