问题解决——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客户端的时候，没有发生字符串转化问题。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-689810/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-689810/