[20201218]dbms_output.put_line無法輸出前面的空格.txt

[20201218]dbms_output.put_line無法輸出前面的空格.txt

--//昨天別人問的問題，自己測試看看。

1.環境：
SCOTT@book> @ 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

2.測試：
SCOTT@book> @ desc_proc sys dbms_output put_line
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- ----------
SYS        DBMS_OUTPUT          PUT_LINE                                1 A                    VARCHAR2             IN        N

SCOTT@book> set serveroutput on
SCOTT@book> exec dbms_output.put_line ('   this is a test');
this is a test
PL/SQL procedure successfully completed.

--//感覺輸入引數做了處理。

SCOTT@book> exec dbms_output.put_line (chr(32)||chr(32)||'   this is a test');
this is a test

PL/SQL procedure successfully completed.
--//^_^依舊。

3.上網檢索發現：


--//連結給出幾種解決方法：
SCOTT@book> set serveroutput on
SCOTT@book> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
--//實際上預設是WORD_WRAPPED。
SCOTT@book> exec dbms_output.put_line(lpad('this', 8, ' ')||' is a test');
this is a test
PL/SQL procedure successfully completed.
--//這樣跟我前面測試一樣不行。

SCOTT@book> SET SERVEROUTPUT ON FORMAT WRAPPED
SCOTT@book> exec dbms_output.put_line (chr(32)||chr(32)||'   this is a test');
     this is a test

PL/SQL procedure successfully completed.

SCOTT@book> SET SERVEROUTPUT ON FORMAT TRUNCATED
SCOTT@book> exec dbms_output.put_line (chr(32)||chr(32)||'   this is a test');
     this is a test
PL/SQL procedure successfully completed.

--//最後，注意完成要選擇關閉，不要顯示檢視執行計劃出現問題。
SCOTT@book> set serveroutput on
SCOTT@book> select * from dept where rownum=1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.

SCOTT@book> set serveroutput off