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

SQL*Plus built-in editing

Of course, you do get some rudimentary editing by default in SQL*Plus.
To aid a quick re-cap ( and probably for use later on), let’s knock up some quick test data…

For the less squemish ( or Australian) among you, you may want to amend this query a little.
For example, you may want to change the predicate.
To do this simply list the line you want and use the change syntax…

Installing rlwrap

The rlwrap package can be found in most of the major distro repositories.
In my case, I want to install it on Mint which, like Ubuntu, is a Debian derivative.
So, open a Terminal window and (in the Debian world, at least)…

Now, if we want to get back to the last of those commands, we can simply press the Up arrow…hey presto, the command magically appears back at the prompt and we can re-execute.
We can scroll up and down the command history simply by using the Up and down arrows.
Editing is just as simple. No messing about with vi commands or arcane SQL*Plus syntax here. Simply use the arrow keys/backspace/delete keys as you normally would.

But is it secure ?

Obviously, rlwrap needs to keep a history of your commands somewhere. In this case, the somewhere is in $HOME/.sqlplus_history.
If you connect to another account in your sqlplus session…

conn hr/pwd@XE

…and then take a look at the history file…

$ grep -i conn .sqlplus_history
conn hr/pwd@XE
$

…things don’t look too clever. To avoid this, you need to use the connect command without a password.
When prompted for it, rlwrap will not record the password :

SQL> conn hr@XE
Enter password:
Connected.
SQL>

Now when we check the history :

$ grep -i conn .sqlplus_history
conn hr@XE
$

TAB completion

In addition to command line recall, you can also use rlwrap to implement TAB completion in SQL*Plus.
To do this, we need to create a file containing the standard keywords we want to complete. Then we need to point rlwrap to this file.
For now, we can just put it in our home directory and then invoke rlwrap from there.
So, the file is simply a text file that looks like this ( I’ve called it keywords.txt) :

select
from
where

Now to invoke rlwrap. This time we’ll use a couple of switches on the command line.
The -f switch points rlwrap to our file.
The -i switch tells rlwrap to ignore case when tab-completing.
Here we go then….

rlwrap -if keywords.txt sqlplus uid@db

Now, at the SQL prompt, if you type ‘SEL (or even ‘sel’) and hit TAB, then rlwrap will auto-complete the word ‘select’ for you.

Linuxifying rlwrap

No, I haven’t just made that word up. Someone else has already done it.
At this point you could start looking at the process of building the appropriate keyword list.
Or you could offer your thanks to a certain Johannes Gritsch, who has already done this for you and head over to Linuxification, where you can download the latest keyword list for Oracle 11gR2 to be used with rlwrap.NOTE – if you decide not to use this option, I’ll cover how you can automate using rlwrap with sqlplus in a bit.

and all of that TAB completion goodness is at your finger tips, together with command-line recall.

Adding an alias

If you prefer your rlwrap without this mayonnaise of TAB completion but don’t want to have to keep remembering to invoke sqlplus with the rlwrap prefix, you can simply add an alias to the .bashrc to remember for you.
In my case (on Mint), I need to do the following …

sudo vi /etc/bash.bashrc

At the end of the file, I’ll add…

alias sqlplus="rlwrap sqlplus"

This will now work from the next time you invoke a terminal session.

If, on the other hand you’ve installed Johannes’ solution, you can instead add :

alias sqlplus="sql+"

NOTE – I have read some stuff about there being problems when using rlwrap with sqlplus in silent mode. I haven’t come across this, but if you do, you may want to re-name your alias to something other than sqlplus ( e.g. sqlh) and use that command instead.
This will avoid the problem as running sqlplus will still invoke the original command.

Right, all that should boost my productivity and give me more time to…worry about England’s collapsible middle-order.