SQLPlus, RMAN etc. with Command History and Auto-Completion

Oracle's sqlplus command line interface to the database is fairly primitive and lacks two of the most often used and powerful features found in shells - a history of commands and auto-completion of keywords (upon hitting the tab key). These features can be added quite easily.

Command History
Hans Lub has written a readline wrapper called rlwrap that allows for editing of any keyboard input. RPM packages do not seem to be widely available so I built one myself - it is available here (version 0.30, built on 32 bit CentOS on 10-Sep-2008, requires readline version >= 4.2). To install, simply do

rpm -ivh rlwrap-0.30-1.i386.rpm

See the man page for details and usage examples.

Tab Auto-Completion
Johannes Gritsch has produced some extensions to rlwrap. These extensions consist of

• The list of Oracle keywords, names of all V$ views, complete data dictionary, DBMS_* and UTL_* packages, SQL functions etc.
• A shell script called sql+ that tells rlwrap what valid SQL word delimiters are (since rlwrap was written for the bash shell there are some differences e.g. $ and # are delimiters in bash but not in SQL).

Since the names of Oracle "variables" differ across versions there are extensions available for 9i, 10g and 11g. Download them here and install as follows (for 10g on CentOS/Redhat/Fedora):

wget http://www.linuxification.at/download/rlwrap-extensions-1.00.tar.gz
mkdir rlwrap-ext
tar xzf rlwrap-extensions-1.00.tar.gz -C rlwrap-ext
chown root:root -R rlwrap-ext
sed -e 's#/usr/local/share/rlwrap#/usr/share/rlwrap#' sql+ > sql+.tmp
mv sql+.tmp sql+
chmod 755 sql+
cp rlwrap-ext/sql+ /usr/local/bin/
cp rlwrap-ext/asmcmd /usr/share/rlwrap/
cp rlwrap-ext/rman /usr/share/rlwrap/
cp rlwrap-ext/sqlplus* /usr/share/rlwrap/

To use it, just do one of the following which then invokes rlwrap with the correct options for SQL and keyword list for tab auto-completion

sql+ username/password
sql+ # without arguments it assumes the SYS user connecting as SYSDBA

Nice and simple! Thanks Hans and Johannes - time permitting I'd like to put these two programs together into one RPM package......