If you are a frequent user of Oracle SQL*Plus, you probably also know about a tool called rlwrap. Bare SQL*Plus does not offer command history, arrow-key editing or any type of word completion so it feels like you’re thrown back in the late 90s using a Spartan SQL interface.
Prefixing “sqlplus” with “rlwrap” drastically improves usability as now you can easily edit your commands, recall history and possibly add a list of frequent used words for TAB autocompletion.
Alternatives are Oracle SQLcl, Oracle Developer or 3rd party tools like GQLPlus or Quest TOAD/SQL Navigator.
But for those who have to live with the natively provided SQLPlus, wrapping it in rlwrap offers an excellent user experience. You can even search the sqlplus history (type CTRL-R and enter parts of what you’re looking for). Many more keyboard shortcuts are available much like on the Linux BASH command line.
Autocompletion for frequently used keywords can be done by providing rlwrap with one or more wordlists (using the -f option) or placing a keyword file named sqlplus in rlwrap’s “completions” folder. See for example:
tanel-poder-scripts-and-tools-short (Tanel Poder)
How to use vi-style editing in SQL*Plus (Martin Bach)
Add History and Tab Completion to SQLPlus (Oracle Alchemist)
rlwrap for Command Line History and Editing in SQL*Plus and RMAN on Linux (Oracle Base – Tim Hall)
Oracle rlwrap tips (Burleson)
The rlwrap utility for DBA (Pythian – Edwin Wang)
SQL*Plus’s Forgotten History (PDF) (Dutch Prutser – Harald van Breederode)
rlwrap-ext – an extension for rlwrap (Linuxification blog)
One thing the Linux (bash) shell does, but rlwrap/sqlplus doesn’t, is auto-complete filenames (in particular, the names of frequently used SQL scripts).
In SQLPlus, you can use an environment variable $SQLPATH (or $ORACLE_PATH). This allows you to run scripts from a central script directory without having to specify the whole path.
So if you set SQLPATH=/usr/share/sql and there is a script /usr/share/sql/myscript.sql, then in SQLPlus you can do SQL> @myscript
without entering the entire path.
The problem is that you may have hundreds of scripts there, and if your brain works like mine then you always forget the exact name of that script – so you need to go to another shell and look it up.
What not many people may realize, is that rlwrap can be extended with plugins which are called “filters” and reside in the /filters directory. A bunch of example filters are included in the package. Filters are written in Perl (in later versions, Python is an alternative).
Introducing SQLWrap
So I wondered if it was possible to use rlwrap filters to provide TAB completion for SQL scripts in $SQLPATH, and this is exactly what SQLWrap does. If you install “sqlwrap” and re-login, then sqlplus is aliased to sqlwrap, which calls “rlwrap sqlplus” with the right parameters to make it work.
Install instructions for SQLWrap can be found here: SQLWrap Install or you may want to look at the SQLWrap man page first.
Then in SQLPlus you can type @ and you will see all .sql scripts in $SQLPATH as well as the .sql scripts in the current directory. Typing the first few characters and pressing TAB autocompletes.
When using wordlists with a large number of keywords, rlwrap passes all keywords to the filter every time TAB is pressed. This makes the whole experience sluggish and almost un-workable. The way I solved this is to have the Perl plugin read the wordlists itself, once, when starting up, and only completing words if the user typed at least 2 characters. A set of keywords is included in the package (6000+ words).
SQLWrap Demo (view image in browser to enlarge): 
Enjoy!
This post first appeared on Dirty Cache by Bart Sjerps. Copyright © 2011 – 2019. All rights reserved. Not to be reproduced for commercial purposes without written permission.
