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;

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

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

11 thoughts on “the sqlplus settings I like

  1. Laurent Schneider

    i do not recommend to have

    set serverout on size 1000000

    as default, because it may make your plsql procedure fails, if it is outputting more than 1M.
    set serverout off
    is the correct default

  2. Web Developer

    Hey Laurent,

    thanks for the tips. The default output of sqlplus was driving me up the wall!

    Also, I like the wp template you’re using. And, do you find Google Friend Connect useful? I’m toying with it at the moment but can’t decide if it’s going to add value.



  3. Steve

    Thanks for tip on command mode versus scripts. Solved my problem of SQL appearing in spooled output.

  4. Mark

    Just used the “SET LONG” to export entire clob field in sqlplus. Thanks for the great tip!

  5. Pingback: Laurent Schneider » Do you know the ORA- nonerrors?

  6. Craig

    sqlplus / <> If I was to use sqlplus in ‘command’ mode (not called with @) as above, is there any parameter or any way to invoke “set echo on|off” and “set termout off|on”….some type of workaround in order to eliminate the need for @script.sql????


  7. Laurent Schneider Post author

    What is your requirement? The short answer is : “no”.

    Sometimes I simply use prompt to do the echo on:

    PROMPT SQL> select * from dual;;
    select * from dual;

    and COLUMN NOPRINT to do the termout off

    SQL> col dummy nopri
    SQL> select * from dual;

Comments are closed.