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:

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



5 thoughts on “How to use vi-style editing in SQL*Plus

  1. Ludovico Caldara

    really nice post! you should only change the link for rhel5 package: both links point to rhel6 package.
    can you also post your sql.dict? just to avoid to reinvent the wheel…
    best regards

    1. Martin Bach Post author

      Ah, the good old copy and paste error. Thanks for the tip! I’ll try to upload the sql.dict file in a separate post. The important bit to remember is not too overload it, but keep it to the commands you use often, and those which are reasonably unique. Otherwise you’ll be typing the whole word anyway :)

  2. Seth Miller

    Thanks for the post. My presentation at Openworld this year included using rlwrap for sqlplus, rman, and asmcmd, but I wasn’t aware of this functionality. I will try it out and add it for the next presentation.

  3. Pingback: Log Buffer #275, A Carnival of the Vanities for DBAs

Comments are closed.