How to use vi-style editing in SQL*Plus

This post is nothing new, and I created it after a little discussion on twitter about how to use readline support in SQL*Plus. The idea is not new, and I have compiled and used rlwrap for quite some time.

At the time, Frits Hoogland asked me why I didn’t use the EPEL package-and I had to admit to myself that I didn’t know the Extra Package for Enterprise Linux repository at all. But there is more to rlwrap and Linux I didn’t know, but first things first.

Installing rlwrap from EPEL

This is really simple-you can either add the EPEL repository to your /etc/yum.repos.d/ directory or simply download the rlwrap package and install it via RPM. A simple wget on your host does the trick. You can set environment variables when you’d like to use a proxy as shown here:

$ export http_proxy=http://your.proxy.server:proxyPort/
$ export https_proxy=https://your.proxy.server:proxyPort/

Depending your release of Enterprise Linux, you can find the rlwrap package here:

• http://dl.fedoraproject.org/pub/epel/5/x86_64/repoview/rlwrap.html
• http://dl.fedoraproject.org/pub/epel/6/x86_64/repoview/rlwrap.html

Then wget should download the file for you, at the time  of writing 0.37 was current.

Defining the alias

Do not succumb to the temptation to use an alias sqlplus-that has been said to cause problems in non-interactive sessions. You could for example use this definition, and put it into the oracle user’s $HOME/.profile:

$ alias sq
alias sq='rlwrap -f $HOME/sql.dict sqlplus'

The “-f” flag allows you to define a list of words that will be tab-completed. Don’t add too many words in there or you’d have to type the whole keyword anyway! My sql.dict file has 42 entries which should be enough. You can query v$reserved_words for inspiration

Defining vi editing mode

The readline library will use emacs mode by default. Although I have used xemacs to write my thesis (in LaTeX2e) I have since become a little rustry in its use and shifted to vim instead. To enable vim mode in readline, you need to create an ~/.inputrc file with these lines:

$ cat ~/.inputrc
set editing-mode vi
set keymap vi

WARNING: this will enable vi-mode in your login shell as well!

What you get

The end result is truly stunning! Type sq and get into SQL*Plus. Depending on the contents of your sql.dict file you can tab-complete keywords, and the arrow keys allow you to move up and down in your history. As it is with vi, typing “ESC” to get into command mode, and use the “?” or “/” commands to search in your history etc. All the vi commands you know now work in SQL*Plus, and you can even use the left and right arrow keys to move within the line (as well as the all the other navigation commands too). Isn’t that great? And there is more-rlwrap apparently exists for other UNIX systems too, but I have not verified that yet. Who needs another UNIX besides Linux ;)

References

• http://sysdba.wordpress.com/2006/10/08/how-to-use-rlwrap-to-get-a-command-history-in-sqlplus/
• http://blog.tanelpoder.com/2010/05/07/flexible-sqlplus-command-line-history-with-rlwrap/
• http://cnswww.cns.cwru.edu/php/chet/readline/readline.html