問題解決——Sqlplus與換行前空格（二）

 

下面實驗，使用Linux客戶端，在Linux上執行程式碼，觀察現象。如果現象相同，就證明問題與OS差異無關。

 

[oracle@oracle11g ~]$ pwd

/u01/oracle

[oracle@oracle11g ~]$ cd ..

[oracle@oracle11g u01]$ mkdir testscript  --建立一個單獨目錄，儲存同步指令碼

[oracle@oracle11g u01]$ cd testscript

[oracle@oracle11g testscript]$

 

 

進行試驗。

 

[oracle@oracle11g testscript]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 13 11:25:30 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn scott/tiger@wilson;

Connected.

 

SQL> conn scott/tiger@wilson

Connected.

SQL> set serveroutput on size 10000;

SQL> set echo off

SQL> set feedback off

SQL>

SQL> spool script.sql

SQL> declare

  2    vc_sql varchar2(2000);

  3    source_rec all_col_comments%rowtype;

  4    target_rec all_col_comments%rowtype;

  5  begin

  6    --Get Source Info

  7    select *

  8    into source_rec

  9    from all_col_comments

 10    where table_name='T1' and COLUMN_NAME='COMM';

 11   

 12    --Get Target Info

 13    select *

 14    into target_rec

 15    from all_col_comments

 16    where table_name='T2' and COLUMN_NAME='COMM';

 17   

 18    if (source_rec.comments=target_rec.comments) then

 19       dbms_output.put_line('Two Objects Comments are equal !');

 20    else

 21       vc_sql := 'comment on column '||target_rec.table_name||'.'

 22                 ||target_rec.column_name

 23                 ||' is '''

 24                 ||source_rec.comments||'''';

 25       dbms_output.put_line(vc_sql);

 26       --execute immediate vc_sql;

 27    end if; 

 28  end;

 29  /

comment on column T2.COMM is 'ISO Country

Char Only;'

SQL> spool off;

SQL>

 

 

在Linux環境上，生成指令碼中空格同樣存在。

 

 

--執行結果

[oracle@oracle11g testscript]$ ls -l

total 4

-rw-r--r--  1 oracle oinstall 984 Mar 13 11:26 script.sql

 

[oracle@oracle11g testscript]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 13 11:32:32 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

SQL> conn scott/tiger@wilson

Connected.

SQL> @script.sql

 

Comment created.

 

 

檢查執行效果。

 

SQL> select table_name, comments, dump(comments, 1016) from all_col_comments where table_name in ('T1','T2');

 

TABLE_NAME COMMENTS       DUMP(COMMENTS,1016)

------------------------- --------------------------------------------------------------------------------

T2         ISO Country    Typ=1 Len=22 CharacterSet=AL32UTF8: 49,53,4f,20,43,6f,75,6e,74,72,79,a,43,68,61,

           Char Only;                              

 

T1         ISO Country    Typ=1 Len=23 CharacterSet=AL32UTF8: 49,53,4f,20,43,6f,75,6e,74,72,79,20,a,43,68,

           Char Only;                                  

 

 

可見，在Linux平臺上進行試驗，同樣會出現空格刪除的情況。由此我們也可以徹底排除OS差異引起問題的猜測。

 

額外：在Windows平臺的Oracle上，筆者也進行的相同實驗，結論相同。篇幅原因，就不累述了。

 

 

排除了OS原因，只有可能是提交SQL的工具手段原因。我們觀察到，出現空格刪除現象的提交方式，是透過載入執行指令碼到sqlplus，或者直接在sqlplus提示符下執行。而不發生刪除現象的提交，都是繞開sqlplus透過SQL Windows或者PL/SQL Developer的GUI介面方式提交。

 

 

這樣，原因就可以猜到了。首先是sqlplus對載入檔案命令列的過程中，因為對文字型別sql指令碼內部結構的“不信任”，會自動把每行資料進行一種trim操作，刪除兩端的空格。之後，再執行這個刪除空格結果。sqlplus使用同樣的方式執行命令列。如果在命令列後輸入空格後回車，該工具會自動的將空格刪除。見下面實驗。

 

SQL> create table t (ch varchar2(100));

 

Table created

 

Executed in 0.19 seconds

--插入一個留有空格的字串

SQL> insert into t values ('blank23 

  2  df');

 

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select * from t;

 

CH

---------------------

blank23

df

 

 

進行測算。

 

SQL> col ch for a10;

SQL> col len for a15;

SQL> col dumps for a30;

SQL> select ch,length(ch) len, dump(ch,1016) dumps from t;

 

CH                     LEN DUMPS

---------- --------------- ------------------------------

blank23                 10 Typ=1 Len=10 CharacterSet=ZHS1

df                         6GBK: 62,6c,61,6e,6b,32,33,a,6

                           4,66

 

 

結論和我們的猜想一樣，九個字元和一個換行符，構成了長度為10的字串。看來sqlplus以及他的模仿產品均是這樣。

 

同樣的結構，如果我們使用pl/sql developer中的SQL Windows執行，結果如何呢？

 

--執行命令

insert into t values ('kk 

k')

 

SQL> select ch,length(ch) len, dump(ch,1016) dumps from t;

 

CH                     LEN DUMPS

---------- --------------- ------------------------------------------------------------

blank23                 10 Typ=1 Len=10 CharacterSet=ZHS16GBK: 62,6c,61,6e,6b,32,33,a,6

df                         4,66

 

kk                       4 Typ=1 Len=4 CharacterSet=ZHS16GBK: 6b,6b,a,6b

k                         

 

 

Executed in 0.02 seconds

 

很遺憾，SQL Windows也是採用此種方法。那麼，只有使用PL/SQL指令碼執行了。下面試著書寫程式碼。

 

declare

  vc_sql varchar2(100);

  vc_str varchar2(10);

begin

  --定義一個帶換行的回車符；

  vc_str := 'dk 

  lf';

 

  vc_sql := 'insert into t values (:1) ';

  execute immediate vc_sql

                    using vc_str;

                   

  commit;

end;

/

 

 

執行。

 

 

SQL>

 

PL/SQL procedure successfully completed

 

SQL> select ch,length(ch) len, dump(ch,1016) dumps from t;

 

CH                     LEN DUMPS

---------- --------------- ------------------------------------------------------------

dk                       7 Typ=1 Len=7 CharacterSet=ZHS16GBK: 64,6b,a,20,20,6c,66

  lf                      

 

 

Executed in 0.01 seconds

 

 

同樣，透過pl/sql指令碼執行，但是經過sqlplus，都會將末尾空格刪除。看來，如果是一些語義重要的欄位，如很長的varchar2型別，還要避免使用sqlplus進行提交。

 

 

最後，我們存在兩個疑惑，這裡進行以下說明。

 

首先，既然透過sqlplus是不能進行換行前空格處理的，那麼原始的那個comments是如果新增進去的呢？考慮到開發人員使用pl/sql developer的GUI工具進行建表操作。可能是開發工具在此處是使用直接的提交語句方式給Oracle處理引擎，繞開了sqlplus的語句處理過程。

 

其次，那麼是不是所有這種換行前空格都要被刪除呢？藉助pl/sql字串變數，和直接提交，是可以避免這種事情發生的。