I have no doubt that those who are "full-time" users of sqlplus (that is a native Oracle's textmode client app to the database server), will agree that it provides a spartan and unimaginative command-line interface. Unlike most modern command-line utilities, such as `mysql' (in MySQL) or `psql' (in PostgreSQL), it lacks
* auto-completion (TAB key), not to mention context-sensitive auto-completion (SELECT ... FROM <TAB>)
* command history (UP/DOWN arrow keys)
* incremental search on the history (CTRL-r)
If you make any mistake typing a long statement, you have to re-enter it all over again. In addition, sqlplus also lacks
* an internal call to system $PAGER for paging query output instead of wrapping the text, without having to spool the output to a file
Since most unix/linux terminal emulators (xterm, rxvt, gnome terminal, kde konsole etc) don't support horizontal text scrolling, but wrap text upon the right margin, the use of sqlplus on unix/linux terminals is really horrific. By the way, it's a shame that a multi-billion company like Oracle, which reported nearly $18 billion total revenues ($4.3 billion net income) for this fiscal year, the company that charges $40,000 per CPU for its "World's Number One Database" with nearly 50% market share and considers Linux to be its supported platform for the future, doesn't take care of getting sqlplus finally doing right (mysql and postgresql are also multi-platform products). As Linux has very efficient gnu readline library and several well-known system pagers such as `more', `less', `most' etc, it's really shameful and ridiculous that we have to google for "workarounds". Fortunately, there are nice "workarounds" all over the internet thanks to opensource.
I. Terminator
Whoever is interested in running sqlplus to issue queries producing long-line text output in a virtual terminal wider than physical screen, and doesn't hesitate in-java-written applications, now can download and install a cross-platform GPL terminal emulator called Terminator with advanced features. Terminator provides horizontal and vertical scrollbars for horizontal and vertical scrolling. It's java application (internally calling bash shell) with Ruby startup script, so it requires Java 5 or later and Ruby 1.8 or later to be installed (Ruby is shipped as standard package with RHEL/OEL). Though written in java, Terminator has very good performance and very nice output. Prebuilt Terminator packages are available from software.jessies.org. Here are direct download links for
32-bit RHEL/OEL
http://software.jessies.org/downloads/redhat/org.jessies.terminator.i386.rpm
64-bit RHEL/OEL
http://software.jessies.org/downloads/redhat/org.jessies.terminator.x86_64.rpm
Just install the package (rpm -ivh) and start terminal by typing `terminator'. By default, the terminal window appears in black background and white foreground. I recommend changing default settings to obtain better-looking screen fonts. Go to Edit/Preferences, select the "Anti-alias text", click on the "Colors" tab and change background to white and text foreground to black, or just click preset "Black on White". Click on the "Save" button to save the settings. Restart the terminal.
II. rlwrap
Linux users working with sqlplus have an additional opportunity to use a readline wrapper `rlwrap' inside Terminator (or any other terminal). Since sqlplus is not built against readline library, rlwrap is just doing the job. It can be downloaded as a source tar package from
http://utopia.knoware.nl/~hlub/rlwrap/
Much better option is to download the newest source rpm package from Fedora 7, eg:
ftp://ftp-stud.hs-esslingen.de/pub/fedora/linux/releases/7/Everything/source/SRPMS/rlwrap-0.28-2.fc7.src.rpm
and build the binary rpm package by
# rpmbuild -bb rlwrap.spec
After installing the package, put the following line in /etc/bashrc (globally) or in ${HOME}/.bashrc (locally):
alias sqlplus='/usr/bin/rlwrap -m ${ORACLE_HOME}/bin/sqlplus'
or whatever your <path_to_sqlplus> is. Relogin or just source .bashrc to take the effect. Start `terminator' terminal and then start "new" sqlplus inside it. Don't forget to SET LINESIZE to a desired value (practically unlimited), or just put the line in the glogin.sql.
Now you'll have an environment similar to that in windows (up/down arrows for command history, Ctrl-r for incremental search on the history, TAB key for auto-completion, all that in a terminal that doesn't wrap the output. It's a good practice to create your own `${HOME}/.sqlplus_completions' file (locally) or `/usr/share/rlwrap/sqlplus' file (globally) with all SQL reserved words (or whatever you want) as your auto-completion list (see rlwrap man page).
Have a lot of fun.
N.J.