下面实验,使用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
2vc_sql varchar2(2000);
3source_rec all_col_comments%rowtype;
4target_rec all_col_comments%rowtype;
5begin
6--Get Source Info
7select *
8into source_rec
9from all_col_comments
10where table_name='T1' and COLUMN_NAME='COMM';
11
12--Get Target Info
13select *
14into target_rec
15from all_col_comments
16 where table_name='T2' and COLUMN_NAME='COMM';
17
18if (source_rec.comments=target_rec.comments) then
19dbms_output.put_line('Two Objects Comments are equal !');
20else
21vc_sql := 'comment on column '||target_rec.table_name||'.'
22||target_rec.column_name
23||' is '''
24||source_rec.comments||'''';
25dbms_output.put_line(vc_sql);
26--execute immediate vc_sql;
27end if;
28end;
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 COMMENTSDUMP(COMMENTS,1016)
------------------------- --------------------------------------------------------------------------------
T2ISO CountryTyp=1 Len=22 CharacterSet=AL32UTF8: 49,53,4f,20,43,6f,75,6e,74,72,79,a,43,68,61,
Char Only;
T1ISO CountryTyp=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
2df');
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;
CHLEN DUMPS
---------- --------------- ------------------------------
blank2310 Typ=1 Len=10 CharacterSet=ZHS1
df6GBK: 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;
CHLEN DUMPS
---------- --------------- ------------------------------------------------------------
blank2310 Typ=1 Len=10 CharacterSet=ZHS16GBK: 62,6c,61,6e,6b,32,33,a,6
df4,66
kk4 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;
CHLEN DUMPS
---------- --------------- ------------------------------------------------------------
dk7 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字符串变量,和直接提交,是可以避免这种事情发生的。