DBA Tips Archive for Oracle

  


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

by Jeff Hunter, Sr. Database Administrator

Contents

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.

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.

 

While developing a list of words and passing them to rlwrap doesn't take much effort, it should be noted that it cannot perform any type of sophisticated or intelligent context-dependent auto-completion. For example, the command-line interface for PostgreSQL automatically pulls the list of potential table names after doing SELECT * FROM <TAB>. Given the generic nature of rlwrap, it cannot perform this type of intelligent context-dependent auto-completion with Oracle sqlplus or rman, but it is better than nothing!

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 server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, 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 20 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 and Mathematics.



Copyright (c) 1998-2017 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 jhunter@idevelopment.info.

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: 13609