SQL*Plus & Command History with Cygwin + rlwrap

Although nearly all apps today are use some sort of point-click, windowing front end, there are plenty of us who still use the command-line when available.  It's not that we're set in our ways, it's that for many repetative tasks, the command-line provides us with enough shortcuts that we're just way more efficient with a few key strokes.

This brings me to Oracle's SQL*Plus, or more specifically, running "sqlplus" from the command-line.  "sqlplus" has long provided the ability to edit the previous command in it's buffer, either pulling it up into an editor or using a few commands to change it in place (such as "CHANGE//").  But obviously this is limited.

In comes "rlwrap".  This application is also nothing new, but did you know it's available from within Cygwin?  This issue came up during Tanel Poder's recent Training Day at Hotsos Symposium 2010.  He gave some great examples of "rlwrap"'s power using it from Linux, then laid the gauntlet down by saying it should work under Cygwin, but he hasn't tried it and we should let him know if we can get it working.  That was enough for me!

I've used Cygwin for some time as a way to start an XWin server, in support of other apps that require it (such as Oracle's installer).  I reviewed the my installation source (x.cygwin.com) and ran the installation again to see if "rlwrap" is available.  Sure enough, if you change the View to be by Category, then you'll file "rlwrap" under Utils:

In the screenshot above you can see "rlwrap" is already installed, but this was taken from my current install, so you get the idea.

The installation process dumps "rlwrap" under c:\cygwin\bin by default.  Now comes the power of the tool!

From your PC, open a Cygwin XWin server to work off of, then an xterm from this XWin server session.  From the xterm session you can fire off your "rlwrap" commands.  You can run them from the XWin server session, but I prefer to instead use that as a central session, firing off separate xterm sessions per connection I plan on making.  In my $HOME directory I created a setup script which defines a series of aliases, one per color-coded xterm screen:

alias black="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg black -fg gainsboro &"

alias brown="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg brown -fg gainsboro &"

alias blue="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkblue -fg gainsboro &"

alias darkred="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkred -fg gainsboro &"

alias green="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg green4 -fg gainsboro &"

alias purple="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg purple4 -fg gainsboro &"

alias teal="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkcyan -fg gainsboro &"

alias white="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg white -fg black &"

This way typing "blue", for example, quickly gives me a new blue background, white foreground xterm.

Back to "rlwrap" power.  A simple example of the command is as follows:

rlwrap -r sqlplus dherri@ORCL1

Within this sqlplus session I now have full command-line history using the arrow keys.  Plus with the "-r" argument, "rlwrap" will "remember", so to speak, all text that passes on the screen.  What this means is through the TAB key you can now quickly access output from previous queries.  For example, suppose you wanted to query an AWR view but couldn't remember the name.  So you enter:

SELECT view_name

  FROM dba_views

 WHERE view_name LIKE 'DBA_HIST%'

 ORDER BY 1;

Now the output list of view names is in rlwrap's buffer, which means the view names are available for command completion.  If you enter:

SELECT * FROM DBA_HIST_SQL

… and hit TAB twice, you'll get the following displayed:

DBA_HIST_SQLBIND              DBA_HIST_SQLTEXT              DBA_HIST_SQL_PLAN             DBA_HIST_SQL_WORKAREA_HSTGRM

DBA_HIST_SQLSTAT              DBA_HIST_SQL_BIND_METADATA    DBA_HIST_SQL_SUMMARY    

This is just a small sampling of what "rlwrap" can provide in terms of added efficiency within sqlplus command-line or for nearly any app for that matter (RMAN quickly comes to mind).  The main point I'm raising here is Windows users need not be excluded from the benefits of command-line completion using "rlwrap".

For more detail on "rlwrap" and excellent examples of applying its use, see Tanel Poder's blog @ blog.tanelpoder.com or his new tech site @ tech.e2sn.com.