Getting a File Listing from a Directory in PL/SQL

It’s General Election time here in the UK.
Rather than the traditional two-way fight to form a government, this time around we seem to have a reasonably broad range of choice.
In addition to red and blue, we also have purple and – depending on where you live in the country, multiple shades of yellow and green.
The net effect is to leave the political landscape looking not so much like a rainbow as a nasty bruise.

The message coming across from the politicians is that everything that’s wrong in this country is down to foreigners – Eastern Europeans…or English (once again, depending on your location).
Strangely, the people who’ve been running our economy and public services for the last several years tend not to get much of a mention.
Whatever we end up choosing, our ancient electoral system is not set up to cater for so many parties attracting a significant share of support.

The resulting wrangling to cobble together a Coalition Government will be hampered somewhat by our – equally ancient – constitution.

That’s largely because, since Magna Carta, no-one’s bothered to write it down.

In olden times, if you wanted to find out what files were in a directory from inside the database, you’re options were pretty undocumented as well.
Fortunately, times have changed…

What I’m going to cover here is how to use an External Table pre-process to retrieve a file listing from a directory from inside the database.
Whilst this technique will work on any platform, I’m going to focus on Linux in the examples that follow…
Continue reading

Installing VirtualBox on Mint with a CentOS Guest

Christmas is almost upon us. Black Friday has been followed by Small Business Saturday and Cyber Monday.
The rest of the month obviously started on Skint Tuesday.
Fortunately for all us geeks, Santa Claus is real. He’s currently posing as Richard Stallman.
I mean, look at the facts. He’s got the beard, he likes to give stuff away for free, and he most definitely has a “naughty” list.

Thanks to Santa Stallman and others like him, I can amuse myself in the Holidays without putting any more strain on my Credit Card.

My main machine is currently running Mint 17 with the Cinnamon desktop. Whilst I’m very happy with this arrangement, I would like to play with other Operating Systems, but without all the hassle of installing/uninstalling etc.
Now, I do have Virtualbox on a Windows partition, but I would rather indulge my OS promiscuity from the comfort of Linux… sorry Santa – GNU/Linux.

So what I’m going to cover here is :

  • Installing VirtualBox on a Debian-based distro
  • Installing CentOS as a Guest Operating System
  • Installing VirtualBox Guest Additions Drivers on CentOS

Continue reading

Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum. Continue reading

Installing SQLDeveloper 4 on Mint and Ubuntu – Minus the Alien

Deb recently bought a new kettle.
Now, a kettle used to simply boil water and turn itself off when it was done.
Not this thing.
It lights up as it boils and announces the fact that it’s finished with a melodious ping.
It’s got gauges and lights and switches.
I’ve decided that it’s probably a Dalek in disguise.
Like Daleks (or at least, the original Daleks), it can’t go up stairs – or if it can, it’s not advertising the fact.
Every morning, descending to the kitchen is filled with trepidation.
When will the Dalek tire of vaporizing innocent water molecules and move on to World Domination…

Doc-tor ! Doc-tor ! I feel like a ket-tle !

Doc-tor ! Doc-tor ! I feel like a ket-tle !

I wouldn’t be entirely surprised to find that, like most whizzy modern appliances, it runs on Java.
Which brings us, by a fairly circuitous route, to the topic at hand – SQLDeveloper.

Oracle’s latest incarnation of it’s IDE does indeed run on Java – the version 7 JDK to be precise.
In this post, I’ll go through the steps required on Mint to :

  • Install the Java 7 JDK
  • Install SQLDeveloper 4
  • Persuade SQLDeveloper 4 to play nicely with Java
  • Add SQLDeveloper to the Cinnamon Menu

The good news is that we can do all of this without the messy alien conversion of an rpm package to .deb format.

NOTE – I’ve followed these steps on Mint13, but they should be pretty much the same for any Debian Distro.
Anyway, without further ado…

Continue reading

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

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