Set
SET SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]}]
A switch of set whether SQLPLUS will display output of store procedure or plsql block.It has no effect on pure sql. Size try to set the output buffer. You can only set value between 2000 and 1000000. Let us see several examples;
15:39:34 lab@ORCL>set serverout off
15:40:16 lab@ORCL>select 1 from dual;
1
----------
1
15:40:21 lab@ORCL>begin
15:40:27 2 dbms_output.put_line('sdfsf');
15:40:29 3 end ;
15:40:31 4 /
15:40:32 lab@ORCL>set serveroutput on
15:40:47 lab@ORCL>begin
15:40:51 2 dbms_output.put_line('sdfsf');
15:40:53 3 end ;
15:40:54 4 /
sdfsf
15:40:55 lab@ORCL>set serveroutput on size 3
SP2-0547: size 选项 3 超出范围 (2000 至 1000000)
set feedback on|off|n
For SQL statment, if n is larger than rows returned, it is as same as option "off". It directly show the data. If the option is "on" and the returned rows less then default set of "N", it display both data and "* rows returned" message.
For Store procedure or Plsql block, the difference is on for "PL/SQL 过程已成功完成。", off for nothing.
Let see several examples:
15:56:29 lab@ORCL>set feedback off
15:56:51 lab@ORCL>select 1 from dual connect by rownum<3
15:57:07 2 ;
1
----------
1
1
15:57:08 lab@ORCL>set feedback off
15:57:15 lab@ORCL>select 1 from dual connect by rownum<3;
1
----------
1
1
15:57:18 lab@ORCL>set feedback on
15:57:31 lab@ORCL>select 1 from dual connect by rownum<3;
1
----------
1
1
已选择2行。
15:57:32 lab@ORCL>set feedback 1
15:57:48 lab@ORCL>select 1 from dual connect by rownum<3;
1
----------
1
1
已选择2行。
15:57:50 lab@ORCL>set feedback 7
15:58:04 lab@ORCL>select 1 from dual connect by rownum<3;
1
----------
1
1
15:58:05 lab@ORCL>set feedback off
15:59:21 lab@ORCL>begin
15:59:24 2 dbms_output.put_line('xxxx');
15:59:36 3 end;
15:59:37 4 /
xxxx
15:59:38 lab@ORCL>set feedback on
15:59:45 lab@ORCL>begin
15:59:50 2 dbms_output.put_line('xxxx');
15:59:52 3 end;
15:59:53 4 /
xxxx
PL/SQL 过程已成功完成。
15:59:55 lab@ORCL>set feedback 10
16:00:17 lab@ORCL>begin
16:00:22 2 dbms_output.put_line('xxxx');
16:00:24 3 end;
16:00:26 4 /
xxxx
PL/SQL 过程已成功完成。
set linesize {150|n}
set the length of one row. The default value is 150. see example:
16:15:06 lab@ORCL>set linesize 5
16:15:14 lab@ORCL>select 'xxxxxxxxxx' from dual;
'XXXX
-----
xxxxx
xxxxx
16:15:23 lab@ORCL>set linesize 150
16:15:35 lab@ORCL>select 'xxxxxxxxxx' from dual;
'XXXXXXXXX
----------
xxxxxxxxxx
set heading on|off
to display the column header or not. see example:
16:15:42 lab@ORCL>set heading off
16:17:47 lab@ORCL>select 'xxxxxxxxxx' from dual;
xxxxxxxxxx
16:17:48 lab@ORCL>set heading on
16:17:53 lab@ORCL>select 'xxxxxxxxxx' from dual;
'XXXXXXXXX
----------
xxxxxxxxxx
set pagesize
very common used in Report request. It shows how manys rows one page can have.
set echo on|off AND set termoutput on|off
This two commands are used to display output for running script in sqlplus.
"set termout on" will display the data return by running the script. "set termout off" will not.
"set echo on " will display the command we run just now. "set echo off" will not. So if want to see effect of "echo on", you need "termout on" firstly. See examples:
16:31:00 lab@ORCL>get d:/test_echo.sql
1* select 1 from dual;
16:31:06 lab@ORCL>set termout on
16:31:14 lab@ORCL>set echo on
16:31:19 lab@ORCL>@d:/test_echo.sql
16:31:24 lab@ORCL>select 1 from dual;
1
----------
1
16:31:24 lab@ORCL>set echo off
16:31:33 lab@ORCL>@d:/test_echo.sql
1
----------
1
16:31:34 lab@ORCL>set termout off
16:31:43 lab@ORCL>@d:/test_echo.sql
16:31:51 lab@ORCL>set echo on
16:31:56 lab@ORCL>@d:/test_echo.sql
set escape {/|c|on|off}
define the escape character of SQLPLUS. It is off by default. You also change the escape charater by yourself. see example
16:37:02 lab@ORCL>show escape
escape "/" (hex 5c)
16:37:09 lab@ORCL>select * from tab t where t.tname ='/&t_name';
未选定行
16:37:38 lab@ORCL>set escape off
16:37:55 lab@ORCL>select * from tab t where t.tname ='/&t_name';
输入 t_name 的值: 123
原值 1: select * from tab t where t.tname ='/&t_name'
新值 1: select * from tab t where t.tname ='/123'
未选定行
16:38:05 lab@ORCL>set escape on
16:38:17 lab@ORCL>set escape $
16:39:02 lab@ORCL>select * from tab t where t.tname ='/&t_name';
输入 t_name 的值: 123
原值 1: select * from tab t where t.tname ='/&t_name'
新值 1: select * from tab t where t.tname ='/123'
未选定行
16:39:13 lab@ORCL>select * from tab t where t.tname ='$&t_name';
未选定行
set flash on|off[No longer used in Oracle 9 or later version]
SET AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
This command is very useful if you want to get some statistic info to do performance tunning. The Precondition is 1)plan_table 2>role plustrace, i will list several high used style.
set autotrace on(have data)
set autotrace off
set autotrace traceonly(have data)
set autotrace traceonly explain(no data)
set autotrace traceonly explain statistics(no data)
set time on|off
display the time in the command prompt. see example
16:39:26 lab@ORCL>set time on
16:46:23 lab@ORCL>set time off
lab@ORCL>
set timing on
ON = display timing statistics for each SQL command or PL/SQL block run.
OFF = suppress timing statistics
examples:
lab@ORCL>set timing on
lab@ORCL>select 1 from dual;
1
----------
1
已用时间: 00: 00: 00.01
lab@ORCL>set timing off
lab@ORCL>select 1 from dual;
1
----------
1