rlwrap and auto completion in sqlplus

It is a while I am following the blog of Mariami Kupatadze, it seems that our background and daily work is around the same area and interest as mine.
This week she posted a simple Linux adjustment (sqlplus backspace – ^H, delete – [[D^, add command history) for better way of using sqlplus.
Since I am also a geek of environment improvements that can ease daily work, I want to add a nice variation.

An example of basic rlwrap usage:

rlwrap sqlplus / as sysdba

or use sqlplus as alias with rlwrap:

alias sqlplus='rlwrap sqlplus'

After some usage you will notice that default history file is created here ~/.sqlplus_history
I usually copy this file to ~/sqlplus.wordlist and edit it with wanted completion word.

My personal file looks this way:

abort
active
affirm
all_roles
alter
alternate
applied
applied_seq#
applied_thread#
apply
apply_finish
apply_lag
archived
archived_seq#
archived_thread#
archiver
async_blocks
attribute
availability
begin
binding
block#
blocks
blocksize
both
bytes
cancel
checkpoint
clear
clob
close
columns
compression
computed
connect
connectdb
convert
count(*)
create
creator
+DATA
database
database_role
datum_time
dba_registry
db_create_file_dest
db_create_online_log_des
dbms_output
db_recovery_file_dest
db_unique_name
declare
decode
delay
delay_mins
desc
descr
dest_id
destination
dest_name
dict
disable
disconnect
distinct
drop
dual
error
escape
estimated
exception
execute
exit
fail_block
fail_date
fail_sequence
failure_count
fal_server
finish
first_change#
flush
force
from
group
group#
gv$instance
gv$log
gv$standby_log
immediate
in-memory
inner
instance_name
instance_role
instanceval
inst_id
into
is_recovery_dest_file
isspecified
join
lag
last_change#
last_time
level
lgwr
like
log_archive_dest
log_archive_max_processes
logfile
log_num_standby
log_size
loop
lpad
managed
max_connections
max_failure
maximize
member
members
message
mount
MRP0
name
net_timeout
next_change#
no_data_found
nomount
null
number
onlinelog
online_logfile
open
open_mode
optional
order
pages
param
parameter
parent_dbun
parent_instance
parent_instancedest_role
pga_aggregate_target
prior
process
protection_level
protection_mode
put_line
recover
redo
register
registrar
reopen
reopen_secs
reset
resetlogs_change#
schedule
scope
select
sequence#
serveroutput
session
sessionval
sga_target
show
shutdown
sid='*'
size
spfile
spool
standby
standby_archive_dest
standby_file_management
standby_file_management='AUTO'
standby_file_management='MANUAL'
start
startup
startup_time
status
stopped
substr
switch
sync
sys_connect_by_path
sysdba
system
table
table_name
target
term
then
thread#
time_computed
timestamp
to_char
to_number
transmit_mode
transport_lag
true
truncate
type
union
unit
used
utl_raw.cast_to_varchar2(hextoraw(value_raw))
valid_for
valid_for=(online_logfile,all_roles)
valid_now
valid_role
valid_type
value
varchar2
v$archive_dest
v$archive_dest_status
v$archived_log
v$archive_gap
v$archive_processes
v$archive_status
v$database
v$dataguard_config
v$dataguard_stats
v$dataguard_status
verify
v$log
v$logfile
v$log_history
v$managed_standby
v$parameter
v$spparameter
v$standby_log
where
while
with
v$dnfs_files
v$dnfs_servers
v$dnfs_channels
@archive_dest
@archived_log
@backup
@dataguard_process
@space
@database
@dataguard_stats
@dataguard_status
@disks
@gap
@managed_standby
@recovery_area_usage
@recovery_progress
@help
@instance
@resize_logs
@asm_orphans
@resize_srls
@resize_logs

Please notice that at the end I added some of my personal scripts. 

Next step is to define the sqlplus alias with the created wordlist file and some more options:

alias sqlplus="rlwrap -pBlue -i -b '()=\!<>&+-*|:;,' -f ~/sqlplus.wordlist sqlplus"

-p prompt color (a  different color then my bash prompt)
-i Ignore case when completing
-b Consider the specified characters as word-breaking
-f completion word list

Now you can enjoy a prompt with its own color, and the most important - your own words for tab completion.

Bye the way, SQLcl has also "auto-complete object names or keywords using the tab key" and it solves history, backspace and delete problems, but I am not sure about your own words :)

One of the benefits of using rlwrap, is that it enables searching via historical commands using Ctrl+R this way:

• At the sqlplus prompt, hold down the Ctrl key and push R. You’ll receive a message that reads (reverse-i-search)`’: followed by the cursor. Type the first letter of a command that you issued before to find it. For instance, if you’ve used the select command, then push s. If you’ve used shutdown, then this might come up as well.
• Push Ctrl+R again to cycle through similarly named commands. If you have a long history, then you might find a number of commands with similar names. Once you find the command that you want to run, push the enter key and your prompt will look like you’ve just typed it in and ran it. You won’t have to type the full command but instead only a letter or two.
• You can type a few letters to find a longer command that you might have issued in the past and it too will come up right away on the command line, and you can then push enter to run it like normal. It makes the process of finding something in your history much easier. Once a command is on the line you can also use the arrow keys and edit it before running it.

Sources:
Installing rlwrap
How to Use CTRL R in Linux
Oracle SQLcl
rlwrap - Linux man page
DBA-010