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
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
Good to know!
Thanks for the great tips concerning CLOB in SQLPUS.
Much appreciated!
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.
Thanks,
Paul.
I did not spent much time on google connect, not sure what it is good for. Feel free to connect anyway 🙂
Thank you for the ‘longc’ tip for CLOB formatting.
Thanks for tip on command mode versus scripts. Solved my problem of SQL appearing in spooled output.
Just used the “SET LONG” to export entire clob field in sqlplus. Thanks for the great tip!
Pingback: Laurent Schneider » Do you know the ORA- nonerrors?
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????
TIA.
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;