[20180920]0x0d0xa.txt

[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