Favorite SQL*Plus tips and tricks
So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job.
I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait for those tools to load, if you are performing some simple queries.
I have a script that I run when I start my shell so I get a better experience:
SET pagesize 2000
SET LONG 10000
SET linesize 1000
COLUMN last_name format a20
COLUMN total format 999,999,999
SET feedback ON
alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ssPM';
I trimmed my "COLUMN" settings for this example, but basically, that helps the data fit on the screen.
Setting the date format really simplifies dealing with dates.
When the command window opens in Windows, I set the window layout properties so I can scroll, have a wider window, etc. and save the settings for future windows.
Does anybody else use SQL*Plus daily? Any tips?
Answer
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.