[20180920]0x0d0xa.txt

--//注:^M 在vim下ctrl+q+ctrl+M(windows下),ctrl+v+ctrl+M(linux下)

--//由于blog无法显示^M,手工使用^ M代替.

--//昨天的测试:http://blog.itpub.net/267265/viewspace-2214629/
--//再做一个例子验证问题:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

D:\temp\test>cat 0x0d0x0a.txt
select '1
2' c10 from dual;

D:\temp\test>od -A x -t x1 0x0d0x0a.txt
000000 73 65 6c 65 63 74 20 27 31 0d 0a 32 27 20 63 31
                               ~~~~~~~~~~~
000010 30 20 66 72 6f 6d 20 64 75 61 6c 3b 0d 0a
00001e

--//可以发现1,2之间是0d 0a.
--//修改如下:
select dump(c10,16) from (
select '1
2' c10 from dual);

SCOTT@test01p> @ 0x0d0x0a.txt
DUMP(C10,16)
---------------------
Typ=96 Len=3: 31,a,32

--//你可以发现丢失0x0d.
--//再次修改如下,这里的^M对应的0x0d.

select dump(c10,16) from (
select '1^M
2' c10 from dual);

SCOTT@test01p> @ 0x0d0x0a.txt
SP2-0734: unknown command beginning "2' c10 fro..." - rest of line ignored.

--//报错,继续修改:
select dump(c10,16) from (
select '1^M22' c10 from dual);

SCOTT@test01p> @ 0x0d0x0a.txt
DUMP(C10,16)
------------------------
Typ=96 Len=4: 31,a,32,32

--//变成0x0a.

D:\temp\test>od -A x -t x1 0x0d0x0a.txt
000000 73 65 6c 65 63 74 20 64 75 6d 70 28 63 31 30 2c
000010 31 36 29 20 66 72 6f 6d 20 28 0d 0a 73 65 6c 65
000020 63 74 20 27 31 0d 32 32 27 20 63 31 30 20 66 72
                   ~~~~~~~~~~~~
000030 6f 6d 20 64 75 61 6c 29 3b 0d 0a
00003b

--//实际上1,2之间的字符是0x0d,oracle在执行后变成了0x0a.
--//在工作中还会遇到的问题有一些开发使用回车作为连接字符串.这样看到的语句会非常奇怪.
--//比如一些PB程序使用~r连接语句

select dump(c10,16) from  ^M
(select '1^M22' c10 from dual) ^M;

--//看到的语句变成这样:

SCOTT@test01p> @ 0x0d0x0a.txt
C10
----------
1
22

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cy1v3hyr1wh5j, child number 0
-------------------------------------
(select '1 22' c10 from dual)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan hash value: 1388734953
------------------------------------------------------------------
| Id  | Operation        | Name | E-Rows | Cost (%CPU)| E-Time   |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1

--//看不到前面的dump相关内容.

--//最后做一个例子:
select '1^M22' c10 from dual
union
select '1'||chr(13)||'22' c10 from dual;

--//按照道理应该输出一行,而实际上结果如下:
C10
----------
1
22

22

select dump(c10,16) from (
select '1^M22' c10 from dual
union
select '1'||chr(13)||'22' c10 from dual
);

DUMP(C10,16)
----------------------------------
Typ=1 Len=4: 31,a,32,32
Typ=1 Len=4: 31,d,32,32