Add History and Tab Completion to SQLPlus

In the uncharted backwaters of the Internet there exists a really awesome tool called rlwrap. This readline wrapper is able to call programs that are deficient in modern amenities like history, color, and tab completion, intercept input/output, and replace it as necessary with useful goodies. And what program could be more deficient than SQLPlus (sorry Tanel)?

With rlwrap you can instantly have history in SQLPlus by pressing the ‘up’ key. Not only that, but the history is remembered even if you exit and come back in later. Tab completion is also possible with a dictionary file, which you can easily populate with your favorite collection of tables, packages, etc. If you’re like me and for whatever reason have never taken to graphical tools but hate the missing features in SQLPlus, this tool is definitely a necessity. So let’s get it installed!

Download and Install

I was able to find an RPM of rlwrap on pbone for EL6 x86_64. You can download it directly here if that is what you are running, or go to this page if you need a different release.

After downloading to the server, I just had to do an RPM install:

[root@orahost ~]# rpm -Uvh rlwrap-0.37-1.el6.x86_64.rpm 
warning: rlwrap-0.37-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:rlwrap                 ########################################### [100%]

Trying it with SQLPlus

Out of excitement I had to try it right away just to see what it could do. Be aware, rlwrap only works with interactive command line so I don’t recommend trying to use it in scripts. Instead, I ran the simple command: rlwrap sqlplus hr/hr

[oracle@orahost ~]$ rlwrap sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 5 20:31:11 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

I was greeted with the familiar prompt and no indication anything was different. But when I typed a command (select * from departments;) and ran it, then pressed up, my previous command appeared. Unfortunately I can’t really show you this easily so you’ll just have to imagine it. Press up on your keyboard. Pretend it showed your last SQLPlus command. Be amazed.

Adding a Dictionary

But now we’re going to do something even better: add a dictionary so we can use tab completion. I generated the dictionary with the following script:

#!/bin/bash

sqlplus -s hr/hr << ! > sqlplus.dict
set head off pages 0 linesize 150 echo off feedback off verify off heading off
select object_name from all_objects where object_type in (
  'TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION'
);
!

You can have this script login as whoever you want (a DBA user would give you every table, package, procedure, and function in the entire DB). When it was finished, I had a nice sqlplus.dict file which contained everything I wanted tab completion to recognize.

We can fire up rlwrap again, this time with the -i flag which tells rlwrap to ignore case for tab complete, and the -f flag which tells it where the dictionary file is.

[oracle@orahost ~]$ rlwrap -if sqlplus.dict sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 5 21:54:15 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from USER_TAB
USER_TABLES                     USER_TAB_COL_STATISTICS         USER_TAB_MODIFICATIONS          USER_TAB_PRIVS_MADE             USER_TAB_STAT_PREFS
USER_TABLESPACES                USER_TAB_COMMENTS               USER_TAB_PARTITIONS             USER_TAB_PRIVS_RECD             USER_TAB_SUBPARTITIONS
USER_TAB_COLS                   USER_TAB_HISTGRM_PENDING_STATS  USER_TAB_PENDING_STATS          USER_TAB_STATISTICS             
USER_TAB_COLUMNS                USER_TAB_HISTOGRAMS             USER_TAB_PRIVS                  USER_TAB_STATS_HISTORY          
SQL> select * from USER_TABLES

When I typed user_tab, I pressed tab three times and it showed me my available choices. Adding the letters le and pressing tab again completed it to USER_TABLES. If there are too many choices you get the familiar “Show all?” message from bash:

SQL> select * from ALL_
Display all 403 possibilities? (y or n)

One important note, it seems the default behavior for CTRL+C is changed when using rlwrap. You have to press CTRL+C then press Enter to cancel your command. Worth it!

Conclusion

Needless to say I instantly decided that I was in love with rlwrap and that it was going to retain a permanent position in my toolset. So I made an alias for it:

alias sql='rlwrap -if ~oracle/sqlplus.dict -pgreen sqlplus'

Now I can type the ‘sql’ command followed by any sqlplus arguments and it will automatically load the dictionary file for me and load up rlwrap. You can name the alias anything you want: sqlplusplus, sqlplussest, omgsql, it doesn’t matter. Just don’t overwrite your actual sqlplus command with the alias, because you will need the original for scripts where rlwrapper does not play nice.

One other thing you may have noticed is the -pgreen. I thought SQLPlus needed a smidgeon of color.

Have fun!