[20130628]sql语句显示不全的问题.txt
今天在优化一条sql语句的时候遇到一个怪问题，sql语句显示有问题，仔细查看才想起来以前看itpub的一本书上讲过，
老杨遇到的问题，语句里面仅仅含有0d（十六进制）编码，自己为了加深印象，做一个测试例子：
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
$ rlsql -s scott/xxxxx
set head off
spool cc.txt
select 'select sysdate '||chr(13)||' from dual;' from dual ;
from dual;ate
spool off
quit
$ xxd -c 16 cc.txt
0000000: 7365 6c65 6374 2073 7973 6461 7465 200d select sysdate .
0000010: 2066 726f 6d20 6475 616c 3b20 2020 2020 from dual;
SQL> @cc.txt
SYSDATE
-------------------
2013-06-28 15:33:13
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID ck9ngpn99pabj, child number 0
-------------------------------------
from dualdate
Plan hash value: 1388734953
-------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | FAST DUAL | | 1 | 2 (0)|
-------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
19 rows selected.
--可以发现sql语句显示" from dualdate".
SQL> select sql_fulltext,sql_text,sql_id from v$sql where sql_id='ck9ngpn99pabj';
SQL_FULLTEXT SQL_TEXT SQL_ID
------------------------------ ------------------------------ -------------
from dual ck9ngpn99pabje
--很明显显示有问题。主要问题是0d是回车，缺少0a表示换行。在一些toad工具就没有这个问题。
--实际上如果执行如下：
SQL> select '111111111111'||chr(13)||'www' from dual ;
'111111111111'||
----------------
www111111111
--显示'www111111111'就很容易明白。