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

 

宣告：本問題的解決過程中，得到了dbsnake老師的幫助指導，特此感謝。

 

 

這幾天在進行環境同步時，發現一個詭異的現象。原有對資料表欄位的comment資訊，明明已經同步並且執行過，並且在字面上看沒有什麼差異。但是，重新執行差異比對指令碼時，還是會認為是有差別，要求進行同步。

 

 

環境準備

 

為重現問題，構建適當的實驗環境。

 

--Linux平臺實驗

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0    Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

SQL> create table t1 (comm varchar2(10));

 

Table created

 

SQL> create table t2 (comm varchar2(10));

 

Table created

 

--透過GUI（PL/SQL Developer的視窗輸入t1.comm的comments資訊）

 

--注意country後面帶有一個空格

comment on column T1.COMM is 'ISO Country

Char Only;';

 

 

此時，為了觀察清楚字元的資訊結構，我們使用dump函式來檢視comm資訊。

 

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                             NULL

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;                                                                      

 

請注意幾個細節，首先T1資料表comments資訊長度為23個字元，其中a表示換行回車，之前的20表示第一行資料結尾的空格。說明字串是先有一個空格，之後進行換行。

 

 

同步指令碼

 

現在的目標是將T1的comments資訊，同步到T2的欄位上。為此，我們書寫了如下程式碼片段。

 

 

spool d:\script.sql

declare

  vc_sql varchar2(2000);

  source_rec all_col_comments%rowtype;

  target_rec all_col_comments%rowtype;

begin

  --Get Source Info

  select *

  into source_rec

  from all_col_comments

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

 

  --Get Target Info

  select *

  into target_rec

  from all_col_comments

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

 

  if (source_rec.comments=target_rec.comments) then

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

  else

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

               ||target_rec.column_name

               ||' is '''

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

     dbms_output.put_line(vc_sql);

  end if; 

end;

/

spool off;

 

上面的程式碼含義是進行差異檢測。如果發現差異，就生成同步ddl語句，並且將語句輸出到磁碟檔案d:\script.sql上。

 

 

同步實驗

 

執行上述匿名程式碼後，執行結果。

 

SQL>

Started spooling to d:\script.sql

 

comment on column T2.COMM is 'ISO Country

Char Only;'

Stopped spooling to d:\script.sql

 

 

注意，我們生成的指令碼中，Country字元後面是由空格的！說明發現了差異，並且將差異反映到了同步script.sql中。

 

執行指令碼。

 

 

SQL> @d:\script.sql –執行指令碼

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;                                                                    

 

 

 

怪事發生了，明明使用原文進行的ddl語句，最後生成的結果是存在差異。詳細對比dump函式結果，可以注意到兩者在長度上有差異，而差異就在回車之前的空格上！

 

那麼，從指令碼到執行的全部過程中，是哪個過程將空格刪除了呢？

 

這時候，存在兩種可能性。其一是作業系統字符集合原因，在windows到linux轉換中，將空格進行刪除。其二是進行提交過程中，一些工具原因造成的。

 

首先，我們考慮作業系統的原因，進行下面兩個實驗來進行驗證。剛才的同步方式，是先將同步指令碼對映在windows平臺檔案，之後透過sqlplus工具呼叫執行。那麼，如果我們不使用平臺檔案，繞過sqlplus工具，透過呼叫pl/sql引擎來執行sql語句。將上述程式碼作出修改。

 

（篇幅原因，省略…）

if (source_rec.comments=target_rec.comments) then

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

  else

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

               ||target_rec.column_name

               ||' is '''

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

     --dbms_output.put_line(vc_sql);

     execute immediate vc_sql; --直接執行sql語句

  end if; 

（篇幅原因，省略…）

 

 

執行後，觀察效果。

 

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=23 CharacterSet=AL32UTF8: 49,53,4f,20,43,6f,75,6e,74,72,79,20,a,43,68,

            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;                                                                      

 

 

發現，藉助直接提交的方式，是可以將回車前面的空格提交的資料庫中。這樣說明了兩方面問題：首先，從comments語句本身來看，回車前面的空格不是問題，Oracle Server是可以儲存空格的。其次，在使用windows客戶端的時候，沒有發生字串轉化問題。