Improve SQL*Plus … before you get crazy!

October 22, 2008  |  linux

Have you ever used backspace key or delete key when writing SQL*Plus statements? Have you ever wanted SQL history like you have on mysql or on any other unix/linux command line ? If yes, this post is for YOU!

Sometimes, some annoying behaviours that developers from big companies don’t watch, drive you crazy! This is the case of SQL*Plus utility.  SQL*Plus is an Oracle command line utility which runs SQL and PL/SQL commands interactively or from a script. Comparing SQL*Plus against other comand line utilities is like comparing MYSQL against Oracle… but in this case MYSQL wins! Even MYSQL has a powerful comand line tool than Oracle. Sometimes, depending on your environment (terminal, client), SQL*Plus behaves strangely when you want to do some complex (ironic) operations… like deleting a character or moving the cursor with the arrow keys.

Today I’llexplain what you can do to improve SQL*Plus to behave properly when youit does not. I’ll also explain how you can easily make SQL*Plus have history of the commands you typed in previous sessions.

Backspace or Delete key behaviour

PROBLEM: It’s simple… SQL*Plus sometimes does not understand what your terminal says to him! Depending on your terminal settings this can affect keyboard behaviour. So if you pressing the BACKSPACE key will print up funny characters: [[D^ and pressing DELETE key will give you this: [[3~. But if you try some keyboard combinations you’ll probably discover that CTRL+BACKSPACE gives you the correct BACKSPACE behaviour you expect.

SOLUTION: What you can do is to change the terminal line settings by using the stty command. If you haven’t yet started SQL*Plus you can type the following command:

[oracle@server~]# stty erase [press CTRL-V followed by BACKSPACE]

If you are already on SQL*Plus then you can run an host command, as you may probably know, by using the ! exclamation signal. In this case after the erase statement just press the backspace!

SQL> !stty erase [BACKSPACE]

If you don’t want to bother with this behaviour in future you may just add a line to your user profile, (.profile, .bash_profile or .cshrc) to make it a permanent change.

Command Line History in SQL*Plus

PROBLEM: When we are used to Linux/Unix shells and we use other command line tools, like SQL*Plus , we miss some nice features like command history. How can he have command line history on SQL*Plus?

SOLUTION: What I’ll explain now is how to tweak your environment with rlwrap. rlwrap is a readline wrapper, a small utility that uses the GNU readline library to allow the editing of keyboard input for any other command. It maintains a separate input history for each command, and can TAB-expand words using all previously seen words and/or a user-specified file.

Let’s start…

[root@server~]# wget -c http://utopia.knoware.nl/~hlub/rlwrap/rlwrap-0.30.tar.gz
[root@server~]# tar xvpfz rlwrap-0.30.tar.gz
[root@server~]# cd rlwrap-0.30
[root@server rlwrap-0.30]# ./configure; make install

You can check that now you have it on your system:

[root@server rlwrap-0.30]# which rlwrap
/usr/local/bin/rlwrap

Next, just add the following line to your .bashrc file on your oracle user home, to add an alias for sqlplus, and we’re done!

alias sqlplus=’rlwrap sqlplus’

Logoff and logon and you can try it on your beautiful SQL*Plus 🙂 … Just remember that this is OS account oriented. If you do some DBA work and then you connect as SCOTT, you’ll also see the history commands that you or anyone else typed logged on as oracle user on that OS.

Hope you like it… Have a nice day…


3 Comments


  1. Hi João,
    It gets even better with rlwrap_ext! Please see http://www.linuxification.at/rlwrap_ext.html.en
    Abraço,

    Georger

  2. it has been very useful.

  3. Combine these two tips for extra shell goodness:

    create sqlplus.sh:

    #!/bin/bash
    stty erase ^H
    rlwrap sqlplus $@
    stty erase ^?

    chmod 0755 sqlplus.sh

    And in ~/.bashrc, add
    alias sqlplus=’~/sqlplus.sh’

    Voila, backspace and command history without screwing up the prompt backspace.

Leave a Reply