the sqlplus settings I like

It is monday, I am going to give a list of settings I like in sqlplus

set lin 32767 trimsp on tab off
extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab off makes sure sqlplus does not use "tab" for formatting, but spaces.

set emb on pages 0 newp none
this avoid page breaks. there is one header in the top, than no more, and no ^L. newp none is not working in version 7, there you must use newp 0.

set head on
set head off
show or hide column headers

set feedb 6
set feedb off
report result of query, set feedb 6 do not give feedback if a select returned 1 to 5 lines, because it is too easy to count... Set feedback off removes feedback

set ver off
I am never interrested in the translation of my defined variables

set termout on
set termout off
Off avoids screen output. Warning, this does not avoid spool output. Works only in scripts, not in command mode. Note that a command piped thru sqlplus is still a command more.

set echo on
set echo off
Display executed command. Works only in scripts, not in command mode.

sqlplus / <<EOF
set echo on
set termout off
select * from dual;
EOF
the echo on and termout off will have no effect, because it is not a sql script (called with @).


def _editor=vi
set editf /tmp/lscfile.sql
Use vi (instead of ed) as editor, and use a file in /tmp (instead of afiedt.buf in working directory) as temp file

set long 1000000000 longc 60000
do not truncate longs nor long chunks. Very usefull with clob in sqlplus.

set serverout on size 1000000
set serverout on size unlimited
allows dbms_output to print to current terminal. Unlimited is a 10gR2 enhancement

set sqlp "_USER @ _CONNECT_IDENTIFIER> "
change the prompt to contain a dynamic user and connection string.

thursday I am having dinner with tom kyte, drop me a comment there if you want to come