DBA Tips Archive for Oracle

Command History and Auto-Completion for SQL*Plus on Unix/Linux

by Jeff Hunter, Sr. Database Administrator

Contents

• Introduction
• Download and Install rlwrap
• Using rlwrap with SQL*Plus and RMAN
• About the Author

Introduction

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.

Download and Install rlwrap

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.

• rlwrap-0.37.tar.gz   (248 KB)

Copy the downloaded source archive file to a temporary location as the "root" user account and install the rlwrap tool as follows.

[root@racnode2 ~]# cp /software/rlwrap/rlwrap-0.37.tar.gz /usr/tmp [root@racnode2 ~]# cd /usr/tmp [root@racnode2 tmp]# gunzip rlwrap*.gz [root@racnode2 tmp]# tar -xvf rlwrap*.tar [root@racnode2 tmp]# cd rlwrap-0.37 [root@racnode2 rlwrap-0.37]# ./configure [root@racnode2 rlwrap-0.37]# make && make install

The rlwrap tool will be copied to /usr/local/bin.

[oracle@racnode2 ~]$ which rlwrap /usr/local/bin/rlwrap [oracle@racnode2 ~]$ rlwrap -v rlwrap 0.37

Using rlwrap with SQL*Plus and RMAN

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.

Command History

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.

alias sqlplus='rlwrap sqlplus' alias rman='rlwrap rman'

After modifying .bash_profile, source the startup script in order to set the alias definitions for the current session.

[oracle@racnode2 ~]$ source ~/.bash_profile

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.

Auto-Completion

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.

[oracle@racnode2 ~]$ echo "select from order where" > library1.dat [oracle@racnode2 ~]$ rlwrap -f library1.dat sqlplus scott/tiger

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

[oracle@racnode2 ~]$ rlwrap -i -f library1.dat sqlplus scott/tiger

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.

[oracle@racnode2 ~]$ echo "select from order where" > ~/.sqlplus_completions [oracle@racnode2 ~]$ echo "advise allocate alter backup crosscheck duplicate restore" > ~/.rman_completions

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:

[oracle@racnode2 ~]$ rlwrap -r sqlplus -S scott/tiger select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production <TAB><TAB> * 11 64bit Database Linux: PL SQL for select where 0 11g BANNER Edition NLSRTL Production TNS from v 1 2 CORE Enterprise Oracle Release Version order version exit [oracle@racnode2 ~]$

Notice how rlwrap automatically added each and every word seen from standard in and standard out to the completion word list.

About the Author

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.