SQL*Plus and RMAN are the two main utilities in Oracle that an Oracle Database Administrator uses a lot to perform day to day activities. Both of these utilities lack some very trivial functionalities like autocompletion of commands, history of old commands, editing of current commands, etc. In this article we will see how to overcome these hurdles by introducing a program called Readline Wrapper (rlwrap) which provides full command line editing support.

RLWRAP is a wrapper program that runs in conjunction with your other command line utilities, Oracle or non-Oracle, to provide history and editing support. The latest version of rlwrap can be downloaded from the web site of Hans Lub, developer of rlwrap, or from here.

The rlwrap program is designed to run on Linux based systems, but it can also be used on Windows by using Cygwin. To install it on Linux you will need C compiler (gcc package) and GNU readline 4.2 or later. In this article we will elaborate the installation and working of rlwrap on Linux.

• Installing rlwrap
• Working with rlwrap

• You can install rlwrap from oracle user but the last few steps require root login. So, if possible, try installing rlwrap as root. Login as root user and place the downloaded file of rlwrap program in root's home directory. Assuming rlwrap-0.30.tar.gz to be the downloaded file, which is a gzipped tar file, we will first unzip it and then untar it as shown below.

[root@khan ~]# file rlwrap-0.30.tar.gz
rlwrap-0.30.tar.gz: gzip compressed data, from Unix, max compression

[root@khan ~]# gzip -dvf rlwrap-0.30.tar.gz
rlwrap-0.30.tar.gz: 74.3% -- replaced with rlwrap-0.30.tar

[root@khan ~]# file rlwrap-0.30.tar
rlwrap-0.30.tar: tar archive

[root@khan ~]# tar -xvf rlwrap-0.30.tar
rlwrap-0.30/
rlwrap-0.30/completions/
rlwrap-0.30/completions/ftp
.
.
.
rlwrap-0.30/TODO
rlwrap-0.30/BUGS

• Untar operation will create a directory named rlwrap-0.30. Change the current working directory to this directory and go through README and INSTALL files before starting the installation.



• To start with the installation execute configure command which will check for all the prerequisites.

[root@khan rlwrap-0.30]# ./configure
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
.
.
.
checking whether your readline knows about rl_readline_version... yes
Will rlwrap find command's working directory under /proc/<commands pid>/cwd? let's see...
checking for /proc/4198/cwd/configure.ac... yes

configure: creating ./config.status
config.status: creating Makefile
config.status: creating doc/Makefile
config.status: creating src/Makefile
config.status: creating doc/rlwrap.man
config.status: creating distribution/rlwrap.spec
config.status: creating config.h
config.status: executing depfiles commands

Now do:
    make (or gmake)  to build rlwrap
    make check       for instructions how to test it
    make install     to install it


• Now execute make command to compile rlwrap program.

[root@khan rlwrap-0.30]# make
make all-recursive
make[1]: Entering directory `/root/rlwrap-0.30'
Making all in doc
make[2]: Entering directory `/root/rlwrap-0.30/doc'
sed -e 's#@DATADIR@#/usr/local/share#' rlwrap.man > rlwrap.1
make[2]: Leaving directory `/root/rlwrap-0.30/doc'
Making all in src
.
.
.
gcc -DDATADIR=\"/usr/local/share\" -g -O2 -o rlwrap main.o signals.o readline.o pty.o completion.o term.o ptytty.o utils.o string_utils.o malloc_debug.o -lutil -lreadline -lcurses
make[2]: Leaving directory `/root/rlwrap-0.30/src'
make[2]: Entering directory `/root/rlwrap-0.30'
make[2]: Nothing to be done for `all-am'.
make[2]: Leaving directory `/root/rlwrap-0.30'
make[1]: Leaving directory `/root/rlwrap-0.30'


• Finally, to install rlwrap, that we compiled in the last step, execute make install command.

[root@khan rlwrap-0.30]# make install
Making install in doc
make[1]: Entering directory `/root/rlwrap-0.30/doc'
make[2]: Entering directory `/root/rlwrap-0.30/doc'
.
.
.
/usr/bin/install -c -m 644 'completions/ftp' '/usr/local/share/rlwrap/ftp'
/usr/bin/install -c -m 644 'completions/testclient' '/usr/local/share/rlwrap/testclient'
/usr/bin/install -c -m 644 'completions/coqtop' '/usr/local/share/rlwrap/coqtop'
make[2]: Leaving directory `/root/rlwrap-0.30'
make[1]: Leaving directory `/root/rlwrap-0.30'


• To get help on how to use rlwrap you can view the man pages of rlwrap by executing man rlwrap. To confirm the installation and get the location of rlwrap program execute which rlwrap as shown below.

[root@khan ~]# which rlwrap
/usr/local/bin/rlwrap

To start using rlwrap program with Oracle utilities follow the steps given below.

• To use SQL*Plus or RMAN utility with rlwrap execute the utility as an argument of rlwrap as shown below.

[oracle@MEMON ~]$ rlwrap sqlplus / as sysdba

• The above command will enable you to view history of commands, edit current or historic commands from command line, and search for commands in history as well. But, the autocomplete feature will still not be available. To use autocomplete feature you will need a dictionary file that will contain all the words that you would like to autocomplete. RLWRAP autocomplete words from the dictionary but not completes the SQL or RMAN commands syntactically. Now, you can either create your own dictionary file or download one that I created, sql.dict, which contains all the reserved words, V$ views, DBA_ views, ALL_ views, USER_ views, RC_ views, DBMS_ packages, initialization parameters, and much more in both upper and lower cases.

• To use rlwrap with complete features, including autocompletion, run SQL*Plus in the following manner.

[oracle@MEMON ~]$ rlwrap -b "" -f $HOME/sql.dict sqlplus / as sysdba

• You can also create an alias in .bash_profile or .profile to save the above command for future usage.

alias sql='rlwrap -b "" -f $HOME/sql.dict sqlplus / as sysdba'

• To view historic commands use UP or DOWN arrow keys.
• To edit current command use RIGHT or LEFT arrow keys.
• To search for a command in history press Ctrl+r and search for a word.
• To edit historic commands use RIGHT or LEFT arrow keys after getting the command by pressing UP or DOWN arrow keys or by searching for them.
• To autocomplete a word press TAB once, if there are multiple choices then pressing TAB once will not work. In this case pressing TAB again will give you all the possible choices.

Leave your comments at