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…

CREATE TABLE top_bats(
    name VARCHAR2(30),
    country VARCHAR2(20),
    runs NUMBER,
    average NUMBER)
/

CREATE TABLE top_bowlers(
    name VARCHAR2(30),
    country VARCHAR2(20),
    runs NUMBER,
    wkts NUMBER,
    average NUMBER)
/    
--
-- Top batsmen - 500 runs or more in the series
--
INSERT INTO top_bats(
    name, country, runs, average)
VALUES(
    'SMITH', 'ENGLAND', 553, 61.44)
/

INSERT INTO top_bats(
    name, country, runs, average)
VALUES(
    'WAUGH', 'AUSTRALIA', 506, 126.50)
/

INSERT INTO top_bats(
    name, country, runs, average)
VALUES(
    'TAYLOR', 'AUSTRALIA', 839, 83.90)
/

INSERT INTO top_bats(
    name, country, runs, average)
VALUES(
    'JONES', 'AUSTRALIA', 566, 70.75)
/

--
-- Top Bowlers - 10 or more wickets in the series
--
INSERT INTO top_bowlers(
    name, country, runs, wkts, average)
VALUES(
    'FOSTER', 'ENGLAND', 421, 12, 35.08)
/

INSERT INTO top_bowlers(
    name, country, runs, wkts, average)
VALUES(
    'ALDERMAN', 'AUSTRALIA', 712, 41, 17.36)
/

INSERT INTO top_bowlers(
    name, country, runs, wkts, average)
VALUES(
    'LAWSON', 'AUSTRALIA', 791, 29, 27.27)
/

INSERT INTO top_bowlers(
    name, country, runs, wkts, average)
VALUES(
    'HUGHES', 'AUSTRALIA', 615, 19, 32.36)
/

INSERT INTO top_bowlers(
    name, country, runs, wkts, average)
VALUES(
    'HOHNS', 'AUSTRALIA', 300, 11, 27.27)
/

COMMIT;

Now, in SQL*Plus we can issue a query such as :

 
SELECT name, runs, average
FROM top_bats
WHERE country = 'ENGLAND'

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…

SQL> SELECT name, runs, average
  2  FROM top_bats
  3  WHERE country = 'ENGLAND'
  4  /

NAME				     RUNS    AVERAGE
------------------------------ ---------- ----------
SMITH				      553      61.44

SQL> l
  1  SELECT name, runs, average
  2  FROM top_bats
  3* WHERE country = 'ENGLAND'
SQL> c/ENGLAND/AUSTRALIA/
  3* WHERE country = 'AUSTRALIA'
SQL> /

NAME				     RUNS    AVERAGE
------------------------------ ---------- ----------
WAUGH				      506      126.5
TAYLOR				      839	83.9
JONES				      566      70.75

…if you want to alter a particular line, you simply need to specify the line number…

SQL> l2
  2* FROM top_bats
SQL> c/bats/bowlers/
  2* FROM top_bowlers
SQL> l
  1  SELECT name, runs, average
  2  FROM top_bowlers
  3* WHERE country = 'AUSTRALIA'

SQL> l1
  1* SELECT name, runs, average
SQL> c/runs/wkts/  
  1* SELECT name, wkts, average
SQL> l
  1  SELECT name, wkts, average
  2  FROM top_bowlers
  3* WHERE country = 'AUSTRALIA'
SQL> /

NAME				     WKTS    AVERAGE
------------------------------ ---------- ----------
ALDERMAN			       41      17.36
LAWSON				       29      27.27
HUGHES				       19      32.36
HOHNS				       11      27.27

SQL> 

You can also add a line…

SQL> l
  1  SELECT name, wkts, average
  2  FROM top_bowlers
  3* WHERE country = 'AUSTRALIA'
SQL> i
  4  ORDER BY average ASC
  5  /

NAME				     WKTS    AVERAGE
------------------------------ ---------- ----------
ALDERMAN			       41      17.36
HOHNS				       11      27.27
LAWSON				       29      27.27
HUGHES				       19      32.36

SQL> 

…or even append text to a line…

SQL> l4
  4* ORDER BY average ASC
SQL> a , wkts DESC
  4* ORDER BY average ASC, wkts DESC
SQL> /

NAME				     WKTS    AVERAGE
------------------------------ ---------- ----------
ALDERMAN			       41      17.36
LAWSON				       29      27.27
HOHNS				       11      27.27
HUGHES				       19      32.36

SQL>

Alternatively, you can even edit the statement in the text editor defined in the _EDITOR SQL*Plus variable…

SQL> DEF _EDITOR=vi
SQL> l
  1  SELECT name, wkts, average
  2  FROM top_bowlers
  3  WHERE country = 'AUSTRALIA'
  4* ORDER BY average ASC, wkts DESC
SQL> ed
Wrote file afiedt.buf
SQL> 

The code is written to the file specified in the editfile variable (afiedt.buf by default).
You can save the text of the current statement into a file…

SQL> l
  1  SELECT name, wkts, average
  2  FROM top_bowlers
  3  WHERE country = 'AUSTRALIA'
  4* ORDER BY average ASC, wkts DESC
SQL> sav nightmare.sql
Created file nightmare.sql
SQL> 

There is a bit of a catch, however. All of this applies only to SQL and PL/SQL statements.
If you issue any other statement, SQL*Plus forgets it immediately…

SQL> set serveroutput on
SQL> BEGIN
  2  dbms_output.put_line('starting to have flashbacks !');
  3  END;
  4  /
starting to have flashbacks !

PL/SQL procedure successfully completed.

SQL> l
  1  BEGIN
  2  dbms_output.put_line('starting to have flashbacks !');
  3* END;
SQL> desc top_bowlers
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME						    VARCHAR2(30)
 COUNTRY					    VARCHAR2(20)
 RUNS						    NUMBER
 WKTS						    NUMBER
 AVERAGE					    NUMBER

SQL> show editfile
editfile "afiedt.buf"
SQL> l
  1  BEGIN
  2  dbms_output.put_line('starting to have flashbacks !');
  3* END;
SQL> 

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)…

sudo apt-get install rlwrap

Now we should be able to take it for a test drive simply by…

rlwrap sqlplus uid@db

Next, we can issue some commands at the prompt…

SQL> desc top_bowlers
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME						    VARCHAR2(30)
 COUNTRY					    VARCHAR2(20)
 RUNS						    NUMBER
 WKTS						    NUMBER
 AVERAGE					    NUMBER

SQL> SELECT sysdate FROM dual;

SYSDATE
------------------
02-JUL-13

SQL> @nightmare.sql

NAME				     WKTS    AVERAGE
------------------------------ ---------- ----------
ALDERMAN			       41      17.36
LAWSON				       29      27.27
HOHNS				       11      27.27
HUGHES				       19      32.36

SQL> 

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.

gunzip rlwrap-extensions-V11-0.05.tar.gz
mkdir rlwrap_extn
cp rlwrap-extensions-V11-0.05.tar.gz rlwrap_etn/.
cd rlwrap_extn
tar xvf rlwrap-extensions-V11-0.05

At this point, it’s probably worth looking over the README file (in my case README.english).
So, we want to create a directory under /usr/local/share then copy the sqlplus* files to that location.

sudo mkdir /usr/local/share/rlwrap/completions
sudo cp sqlplus* /usr/local/share/rlwrap/completions/.
sudo cp adrci /usr/local/share/rlwrap/completions/.
sudo cp asmcmd /usr/local/share/rlwrap/completions/.
sudo cp rman /usr/local/share/rlwrap/completions/.

Finally, we need to copy sql+ and asm+ (if required) to the /usr/local/bin directory…

sudo cp sql+ /usr/local/bin
sudo cp asm+ /usr/local/bin
sudo chmod a+x /usr/local/bin/*+

Now, you simply need to invoke sqlplus using…

sql+ uid@db

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.