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

Oracle Float to Integer fun and Sachin Tendulkar

I recently spent some time working with Venkata, an Oracle programmer and keen cricket fan :

Venkata smiling the smile of a man whose team have won the cricket world cup twice more than...er...Wales.

Venkata smiling the smile of a man whose team have won the cricket world cup twice more than…er…Wales.

This post is about the fun and games involved in the bulk loading of data into Oracle – especially when it includes floating point values.
In Venkata’s honour, I’m going to explore this topic through the medium of the career of Sachin Tendulkar…

At this point it’s probably worth wandering off the point to explore some of the highlights of The Little Master’s career.

India’s tour of England in 1996 has been largely forgotten. However, it proved to be a watershed in our hero’s test career.
The first test of the series at Edgebaston was not untypical of the time – Indian batsmen skittled in conditions totally alien to them. The could only muster a paltry 219 in their second innings and none of them got beyond 18…apart from Tendulkar, ninth out for 122, made with a serenity that contrasted starkly with the chaos surrounding him.
It was in the very next match, at Lords, that India granted test debuts to two batsmen who themselves would prove quite useful over the years. Surav Ganguly marked the occasion with a century. Rahul Dravid fell an agonising five runs short of doing the same.
The fourth member of India’s vaunted middle-order announced himself in the next series we look at, against Australia.
Having been soundly beaten in the first test of the series, India came back the hard way in the second test.
VVS Laxmans 281 not out, with the not inconsiderable assistance of Dravid (180) and Harbhajan (13-196) became only the third team in the history of Test Cricket to win after following on. This against arguably the best team to have ever played the game who had their record winning streak of 16 consecutive tests ended somewhat emphatically.
The 2004/05 series against Bangladesh is included because Tendulkar’s highest test score (248 not out) was made in this series.
The 2009/10 series against South Africa is indicative of the journey that India made during the course of Tendulkar’s career.
At this time they were vying with South Africa for the status of the world’s leading test nation.

Back to the techie stuff. What I’m going to cover is :

  • What happens when you insert a floating point value into an INTEGER column
  • How you can overcome this using SQL*Loader
  • An alternative approach using external tables

Continue reading

Old Dog, New Phone – Insert into multiple tables in a single statement

I got a new ( and possibly new-fangled) phone recently.
Having spent a suitably geeky amount of time exploring the new features of this slab of Android lovliness I did come across something which left me quite stumped… somebody rang me up on it.
Increasingly frantic tapping of the little green phone icon failed to result in me answering the call.
It was at this point that Deb took pity and explained in that ever-so-patient way people use to the hard of thinking, that I simply needed to swipe.
Needless to say, she’s not ever mentioned the incident again and would never dream of taking any opportunity to bring it up to elicit a laugh at my expense. Ahem.
It’s unfortunate that I also got a work phone recently and spent five minutes tapping the screen before I worked out that the Blackberry I was holding was not, in fact, a touch-screen device.
As the fast-paced world of consumer electronics passes by in an ever-increasing blur, I’m comforted by the fact that I can still learn something new.
For example, I was recently faced with a situation requiring me to load data from one table into two further tables, depending on certain criteria.
With the aid of my trusty netbook ( a form-factor that is apparently as outdated as it’s owner), I shall now demonstrate the wonder of the age that is the multi-table insert statement.
Continue reading

An APEX Database Monitoring App for XE – Guilty GUI pleasures

Guilty pleasures. For some, it’s a “diet” burger with “diet” fries, washed down with a “diet” shake. Others have a penchant for Kurt Geiger shoes. “I’m Welsh and I’m worth it”, they may well say. It may even be that Def Leppard track nestled in your playlist between Coldplay and Oasis.

In programming terms, APEX seems to fall into this category for me. On the one hand, it’s a declarative development environment. This means that, unless you’re very careful, the application you write for it is not going to be too portable to other front-end technologies. But, oh, it’s so nice to be able to bang out a bit of SQL and/or PL/SQL, click my mouse in the right place, and have a nice GUI application drop onto my browser.

If you’ve decided to try the latest and greatest APEX version on your XE installation, you’ll notice that the default Database Welcome Page disappears after the upgrade.
Rather than hunting around for it, I’ve decided to knock up something a bit better…well, different.
So, if you’d like to know how to get some interesting configuration information out of the database…or just want the entertainment value of watching me blunder about in APEX then read on… Continue reading

ORA-02030 and invisible objects. The database bites back !

Being Luis Suarez’s agent must be an interesting job right now.
Maybe the man was a bit peckish.
Alternatively, maybe he’s resigned to the FA’s reluctance to introduce a mid-season break and was simply making his own arrangements for time off during the season.
Either way, this particular agent may well be trying to sign Luis up for an ad campaign for a popular brand of toothpaste.

Oracle DBA’s may sometimes have some sympathy with Suarez, although they’re more likely to end up chewing the desk in frustration, rather than their fellow DBA’s (unless the Christmas Party has really gotten out of hand).
Every so often, Oracle throws out an error that, on the face of it, makes absolutely no sense…
Continue reading

Customising the Oracle XE Menu in XFCE – why it’s better to be vegetarian

Roberto Goldbrick. The name of the central character in a biting satire about a Premiership footballer ? Actually, it’s the name of the horse I drew out in the Office Grand National Sweep Stake.

“Oh well”, said Deb as the winner crossed the line with Roberto nowhere to be seen, “by next week it’ll be a value frozen lasagne”.
That’s the thing about vegetarians, they do like to assert their moral superiority at times. It can be quite difficult to find a suitable riposte. After all, you feel a bit of a twit accusing someone of vegicide.

In order to overcome my disappointment, I’ve taken refuge in Mint 14 XFCE running on my netbook.

Using the steps here and here I’ve managed to install Oracle 11gXE without any problems….apart from the fact that the Menu items now appear on the Others menu.
Being a lightweight desktop, XFCE doesn’t provide a default GUI to enable menu editing, so I’ve had to do a bit of investigation…
Continue reading

Facial Hair and Funky Job Titles – A Personal History of the Internet Age

I had a rare technical issue with my blog recently. My e-mailed cry for help was answered and my problem resolved. The helpful soul at WordPress who aided me in my time of need was Jackie, who rejoices in the title of Hapiness Engineer.

I’d imagine that WordPress must have a novel and somewhat refreshing approach to Job titles.
We could apply this to Deb, for example. As a Health Professional working with the under fives, she could be a Small Human Maintenance Consultant.

There are some similarities between Deb’s job and my own. Whilst I have on occasion dealt with people whose job titles include words like ‘Chief’ and ‘Officer’, I also occasionally have to deal with the aftermath of toys being violently ejected from prams.

At the moment however, I find myself leading a team, all of whom are rather younger than me. This presents a number of challenges.

The realisation that I have children that are older than some of my colleagues is particularly acute when we discuss technological issues.
In computing terms, I grew up before the Internet was the all-pervasive presence it is now.
I learned to touch-type many years ago, in order to reduce the number of typos I made whilst writing code.
This is beginning to feel somewhat quaint with the proliferation of declarative development environments and technologies.

I suppose the point of this post is to wander back through the technologies that influenced my career as a programmer and explain why they are so important in shaping the technological landscape we now operate in.
Continue reading