How to make sqlplus more pleasant with rlwrap and a nice prompt

humanize_sqlplus.md

Humanizing sqlplus

It's always been frustrating to me that sqlplus does not have REPL behavior, so you can't arrow-up through your history, etc. In addition, the default SQL prompt is not very informative. Since I often work on prod and dev databases at the same time, it is important to me that I know where I am when I am executing disastrous SQL.

Well, rlwrap to the rescue! Plus, a handy login.sql script to customize your sqlplus prompt. The package was available by default in RHEL6 and on OSX El Capitan. YMMV...

Three Parts

You'll need to do three small things, and you are good to go,

• Add an alias
• Export a path for your login.sql
• Create that location and add your login.sql file

Add an alias

In ~/.bash_aliases, for example

## oracle sqlplus humanizing
alias sqlplus='rlwrap sqlplus'
## what about rlwrap -if?
## http://mikesmithers.wordpress.com/2013/07/06/simple-pleasures-rlwrap-and-sq$
## more awesomeness: http://www.linuxification.at/rlwrap_ext.html.en
## http://www.linuxification.at/download/rlwrap-extensions-V11-0.05.tar.gz

Export a path

In ~/.bash_profile, for example

# for login prompt stuff
export SQLPATH=$HOME/.sql

Create login.sql

Put login.sql in the SQLPATH you just exported, with one line:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL> "

Profit!

Now when fire up a new shell you hit up sqlplus a la

% sqlplus user@somedb

You'll get a nice prompt to remind you where you are

USER@somedb SQL>

and you'll get a navigatable history with arrows, usuable shortcuts, etc. Huzzah!