Add History and Tab Completion to SQLPlus

Share Button

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:

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

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:

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.

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:

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:

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.

SQLPlus With Color

Have fun!

Share Button

3 Responses to “Add History and Tab Completion to SQLPlus”

  1. Oleg Cherkasov says:

    Emacs has a good SQL mode like shell or eshell ones. Try M-x sql-oracle and it is very good wrapper both on Windows and Linux.

  2. rlwrap is part of EPEL repository also. http://fedoraproject.org/wiki/EPEL Good to include this repository, a lot of useful things (including R)

  3. Cool hint with CTRL-C ! I did not know. Another mean to get command history is to switch to MSDOS :)

Leave a Reply