ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 问题解决——Sqlplus与换行前空格(二)

问题解决——Sqlplus与换行前空格(二)

原创 Linux操作系统 作者:realkid4 时间:2011-03-17 22:20:57 0 删除 编辑

 

下面实验,使用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字符串变量,和直接提交,是可以避免这种事情发生的。

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7690302