[20180111]Oracle's Bulk Collect and Limit2.txt
--//昨天在测试时遇到一个的奇怪问题，链接http://blog.itpub.net/267265/viewspace-2149969/,执行
--//@ limit 9 时，重复输出了2次。主要是我的glogin配置文件里面包含如下：
set termout off
call dbms_output.enable(1e6);
set termout on
--//itpub论坛的提问：http://www.itpub.net/thread-2096243-1-1.html
--//缺省设置 set serverout off，而且dbms_output设置为disable，而我在配置文件中打开了即使是set serverout off的情况下。
--//通过一个例子说明问题：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show serveroutput
serveroutput OFF
2.测试：
SCOTT@book> call dbms_output.enable(1e6);
Call completed.
SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a first test!');
PL/SQL procedure successfully completed.
--//现在不显示是正常的。
SCOTT@book> set serverout on
SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a second test!');
this is a first test!
this is a second test!
PL/SQL procedure successfully completed.
--//OK,现在把在缓存的第一行一起显示出来。出现了2行。再次执行就不会了。
SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a second test!');
this is a second test!
PL/SQL procedure successfully completed.
3.回到我当时的出现的问题就很好理解了。
我现在set serverout off情况下执行
set serverout off
@limit 9
set serverout on
@limit 9
--//这样就出现2次显示，感觉重复显示了。主要问题我在set serverout off的情况下打开了dbms_output.enable(1e6)。
--//而这样设置源于去年的这个帖子：http://blog.itpub.net/267265/viewspace-2148591/=>[20171211]dbms_output无serveroutput on
--//这样看来没有想作者想像的这么复杂，只要随手调用1次exec DBMS_OUTPUT.put_line就ok了。
4.测试：
--//退出在登录：
SCOTT@book> exec dbms_output.enable(1e6);
PL/SQL procedure successfully completed.
SCOTT@book> exec dbms_output.put_line('first');
PL/SQL procedure successfully completed.
SCOTT@book> set serverout on
SCOTT@book> exec dbms_output.put_line(' ');
first
PL/SQL procedure successfully completed.
--//退出在登录：
SCOTT@book> exec dbms_output.enable(1e6);
PL/SQL procedure successfully completed.
SCOTT@book> @ limit 9
old 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 9;
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/output_print.sql
PL/SQL procedure successfully completed.
OUTPUT
---------------
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000
10 rows selected.
--//这样的好处不用设置set serverout on就能显示。
