Favorite SQL*Plus tips and tricks [closed]

You can use rlwrap to add readline support to sqlplus. Run sqlplus like this:

$ rlwrap -c sqlplus username@database

Now up/down will scroll through command history. Use ctrl-r to search backwards through history, etc. This makes sqlplus bearable.

Also, add this to your login.sql to set the linesize to whatever the width of your terminal is:

HOST echo "set linesize" $(stty -a|head -n1|cut -f7 -d' '|cut -f1 -d';') > .tmp.sql
@.tmp.sql
HOST rm -f .tmp.sql

Both of these tips only work on unix.

Yes, I use SQL Plus every day in preference to Toad or SQL Developer (though I also use SQL Developer to browse the database).

I have the following in my login.sql script (which SQL Plus runs automatically):

1) Replace default editor (Notepad) with one of my choice:

define _editor = "C:\Program Files\TextPad 5\TextPad.exe"

2) Make SQL prompt show database name so I know where I am (thanks to Tom Kyte for this):

COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name||CHR(10)||'SQL> ' AS global_name
FROM   global_name;
SET SQLPROMPT '&gname'
SET TERMOUT ON

... plus other setting similar to yours.

I also find Tom Kyte's print_table procedure very useful.

Remember that we can put these settings in the login.sql script which will be run automatically whenever we start SQL*Plus. Find out more.

The neat thing about this is, that since 10g, this script is run every time we connect rather just the first time we fire up SQL*Plus...

SQL> conn apc
Enter password:
Connected.
Running login script

Session altered.

SQL> conn scott
Enter password:
Connected.
Running login script

Session altered.

SQL>