Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0
SQL*Plus - Version: 8.1.7
Information in this document applies to any platform.
Purpose
The SQL*Plus, the primary interface to the Oracle Database server,
provides a powerful yet easy-to-use environment for querying, defining, and controlling data.
However, some command-line utilities, for example bash, provide features such as:
- command history (up/down arrow keys)
- auto completion (TAB key)
- searchable command line history (Ctrl+r)
The scope of this bulletin is to provide these features to SQL*Plus.
Scope and Application
For all SQL*Plus users, but in particular for Linux platforms as this note has been written thinking at that Operating System. In any case this idea can work on other OSs as well.
SQL*Plus command line history completion
SQL*Plus users working on Linux platforms have the opportunity to use a readline wrapper "rlwrap". rlwrap is a 'readline wrapper' that uses the GNU readline library to allow the editing of keyboard input for any other command. Input history is remembered across invocations, separately for each command; history completion and search work as in bash and completion word lists can be specified on the command line. Since SQL*Plus is not built with readline library, rlwrap is just doing the job.
- 'rlwrap' is really a tiny program. It's about 24K in size, and you can download it from the official developper (Hans Lub) website http://utopia.knoware.nl/~hlub/uck/rlwrap/
What do you need to compile and run itA newer (4.2+) GNU readline (you can get it at
ftp://ftp.gnu.org/gnu/readline/)
and an ANSI C compiler.
rlwrap compiles and runs on many Unix systems and on cygwin.
Installation should be as simple as:
./configure
make
make install
Compile rlwrap staticallyIf you don't have the root account you can compile rlwrap statically
and install it under $HOME/bin executing this command:
CFLAGS=-I$HOME/readline-6.0 CPPFLAGS=-I$HOME/readline-6.0 LDFLAGS=-static ./configure --prefix=$HOME/bin
make
make install
where $HOME/readline-6.0 is the 'readline' source location
A different option and if your are using Linux, it is to download the newest source rpm package from e.g.: ftp://rpmfind.net/linux/fedora/core/development/source/SRPMS/rlwrap-0.28-2.fc7.src.rpm
and build the binary rpm package by
# rpmbuild -bb rlwrap.spec
and then you can install it as any other rpm package by e.g.
# rpm -ivh rlwrap-0.28-2.fc7.i386.rpm
- After installing the package, you should to configure a user's environment so that it makes use of the installed utility, add the following line in '/etc/bashrc' (globally) or in '${HOME}/.bashrc' (locally for the user). Change '' with the right path of your rlwrap:
alias sqlplus='/rlwrap ${ORACLE_HOME}/bin/sqlplus'The modified .bashrc won't take effect until you launch a new terminal session or until you source .bashrc. So shut down any terminals you already have open and start a new one.
If you now launch SQL*Plus in exactly the way you've used so far, you should be able to type one SQL command and submit it, and then immediately be able to press the up-arrow key and retrieve it. The more SQL commands you issue over time, the more commands rlwrap will remember. As well as just scrolling through your previous SQL commands, you can press 'Ctrl+r' to give you a searchable command line history.
You can also create your own '${HOME}/.sqlplus_completions' file (locally)or '/usr/share/rlwrap/sqlplus' file (globally) with all SQL reserved words (or in case whatever you want) as your auto-completion list (see rlwrap man page for dettails).
And example of '${HOME}/.sqlplus_completions' with some reserved words:
COPY PAUSE SHUTDOWN
DEFINE PRINT SPOOL
DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED UNDEFINE
CHANGE HELP RESERVED VARIABLE
CLEAR HOST RUN WHENEVER
copy pause shutdown
define print spool
del prompt sqlplus
accept describe quit start
append disconnect recover startup
archive log edit remark store
attribute execute repfooter timing
break exit repheader ttitle
btitle get reserved undefine
change help reserved variable
clear host run whenever
ALL ALTER AND ANY ARRAY ARROW AS ASC AT
BEGIN BETWEEN BY
CASE CHECK CLUSTERS CLUSTER COLAUTH COLUMNS COMPRESS CONNECT CRASH CREATE CURRENT
DECIMAL DECLARE DEFAULT DELETE DESC DISTINCT DROP
ELSE END EXCEPTION EXCLUSIVE EXISTS
FETCH FORM FOR FROM
GOTO GRANT GROUP
HAVING
IDENTIFIED IF IN INDEXES INDEX INSERT INTERSECT INTO IS
LIKE LOCK
MINUS MODE
NOCOMPRESS NOT NOWAIT NULL
OF ON OPTION OR ORDEROVERLAPS
PRIOR PROCEDURE PUBLIC
RANGE RECORD RESOURCE REVOKE
SELECT SHARE SIZE SQL START SUBTYPE
TABAUTH TABLE THEN TO TYPE
UNION UNIQUE UPDATE USE
VALUES VIEW VIEWS
WHEN WHERE WITH
all alter and any array arrow as asc at
begin between by
case check clusters cluster colauth columns compress connect crash create current
decimal declare default delete desc distinct drop
else end exception exclusive exists
fetch form for from
goto grant group
having
identified if in indexes index insert intersect into is
like lock
minus mode
nocompress not nowait null
of on option or orderoverlaps
prior procedure public
range record resource revoke
select share size sql start subtype
tabauth table then to type
union unique update use
values view views
Note:
You can use 'rlwrap' with all Oracle command line utilities such as Recovery Manager (RMAN) , Oracle Data Pump (expdp), ASM command (asmcmd), etc.
i.e.:
alias rman='/usr/bin/rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='/usr/bin/rlwrap ${ORACLE_HOME}/bin/expdp'
alias asmcmd='/usr/bin/rlwrap ${ORACLE_HOME}/bin/asmcmd'
References
http://utopia.knoware.nl/~hlub/uck/rlwrap/ftp://ftp.gnu.org/gnu/readlineftp://rpmfind.net/linux/fedora/core/development/source/SRPMS/rlwrap-0.28-2.fc7.src.rpm
