DBA Tips Archive for Oracle
Command History and Auto-Completion for SQL*Plus on Unix/Linux
by Jeff Hunter, Sr. Database Administrator
Oracle sqlplus and rman are popular command-line interfaces that allow for quick and easy access to an Oracle instance. However, unlike most modern command-line tools like MySQL or PostgreSQL, Oracle's sqlplus and rman do not provide persistent command history or auto-completion functionality under Linux and Unix. For example, without command history, making a mistake when typing a long statement in sqlplus often means canceling out and re-entering it all over again.
Powerful interfaces like the Unix/Linux bash shell offer auto-completion of the text you are typing (by pressing TAB), access to a history of previous commands (up/down arrows, or CTRL-P/CTRL-N), incremental search on the command history (CTRL-R), moving the cursor and modifying statements within the current line (left/right arrows), and persisting the command history in between invocations. Nearly all these tools use the GNU readline library to provide these capabilities.
While not all command-line tools use the GNU readline library (like Oracle's sqlplus and rman), the good news is that these limitations can be easily overcome by using the rlwrap tool written by Hans Lub.
rlwrap works by "wrapping" any other command-line tool and gives you a readline interface to it. For example, invoking rlwrap sqlplus from a shell prompt will provide sqlplus with the history capabilities of the readline library.
rlwrap compiles and runs on most Unix/Linux systems, including cygwin. To read more about the rlwrap tool, see the author's manpage and README file.
The remainder of this guide discusses how to download, compile, install, and configure the rlwrap tool for use with Oracle's sqlplus and rman command-line utilities.
On Solaris, rlwrap can be installed with the sunfreeware package whereas on Linux, the easiest way is to compile it on the specific host as described in this section.
At the time of this writing, the latest release of rlwrap is version 0.37 and can be downloaded from the authors website or the following link.
Copy the downloaded source archive file to a temporary location as the "root" user account and install the rlwrap tool as follows.
The rlwrap tool will be copied to /usr/local/bin.
After successfully installing the rlwrap tool, following the instructions in this section to configure both command history and auto-completion functionality for sqlplus and rman.
Edit the .bash_profile for the "oracle" user account (as well as any other user accessing Oracle) and add aliases to run the sqlplus and rman commands as arguments to rlwrap.
After modifying .bash_profile, source the startup script in order to set the alias definitions for the current session.
Log in to either SQL*Plus or RMAN to test the new configuration. Type in a few statements and use the up/down arrow keys to move through the command history as well as the left/right arrow keys to edit the current line.
In addition to command history, rlwrap can also provide generic auto-completion functionality. To utilize this feature, a list of potential words to use for auto-completion is provided to rlwrap. This can be performed using several different methods.
Let's start with a simple example by using the -f file option to rlwrap to specify a file containing words that will be split and added to the completion word list. The following example creates a very small list of potential completion words. It is reasonable to create a much larger completion word list that contains all Oracle dictionary and schema objects along with a list of all PL/SQL commands.
While logged in to sqlplus (above), type in the two characters "se" and hit the TAB key. The auto-completion feature should detect and pull in the word "select". Note that by default, the word completion list is case-sensitive. This can be overwritten using the -i option (this option has to come before any -f options).
While using the -f file option is fairly straightforward, it can become cumbersome and is not very scalable. A more elegant approach is to create a completion word list file for each command you intend to use with rlwrap. This can be done on a per-user basis by creating a completion word list file for each command (i.e. sqlplus, rman, etc.) containing a list of words that each command accepts and using the naming convention ~/.command_completions. For example, let's create two separate completion word list files; one to be used when running the sqlplus command and another when running the rman command.
Now when I launch sqlplus or rman, I don't need to specify the -f file to manually read in a completion word list file. Each command now has a specialized auto-completion file that is read in automatically based on the command being run.
Another method to populate the completion word list for a command is by using the remember option -r. By using the remember option, rlwrap will put all words it sees from standard in and standard out on the completion word list. This includes words from statements you type in and words returned from queries. For example:
Notice how rlwrap automatically added each and every word seen from standard in and standard out to the completion word list.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.
Copyright (c) 1998-2013 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Tuesday, 04-Sep-2012 00:25:39 EDT
Page Count: 5165