[20171220]toad plsql顯示整形的bug.txt

[20171220]toad plsql顯示整形的bug.txt

--//下午有itpub網友反應,一個查詢在sqlplus,pl/sql下不同.連結如下:
--//http://www.itpub.net/thread-2095697-1-1.html
--//我測試感覺是資料出現錯誤.直接那它的資料測試看看.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t ( id number , a  number(15,2));
Table created.

insert into t values (1,7334.00);
insert into t values (2,7334.00);
commit ;

SCOTT@book> select rowid,t.*,dump(a,16) c30  from t;
ROWID                      ID          A C30
------------------ ---------- ---------- ------------------------------
AAAWLoAAEAAAAIkAAA          1       7334 Typ=2 Len=3: c2,4a,23
AAAWLoAAEAAAAIkAAB          2       7334 Typ=2 Len=3: c2,4a,23

--//正常7334的編碼就是c24a23

SCOTT@book> @ &r/conv_n c24a23
old   1: select utl_raw.cast_to_number(lower('&1')) n20 from dual
new   1: select utl_raw.cast_to_number(lower('c24a23')) n20 from dual
       N20
----------
      7334

--//而對方看到的編碼是c248eb,明顯不對.
SCOTT@book> @ &r/conv_n c248eb
old   1: select utl_raw.cast_to_number(lower('&1')) n20 from dual
new   1: select utl_raw.cast_to_number(lower('c248eb')) n20 from dual
select utl_raw.cast_to_number(lower('c248eb')) n20 from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_RAW", line 388

SCOTT@book> @ &r/rowid AAAWLoAAEAAAAIkAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90856          4        548          0  0x1000224           4,548                alter system dump datafile 4 block 548 ;

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> alter system flush BUFFER_CACHE;
System altered.

2.透過bbed修改資料塊:
SCOTT@book> @ &r/bbvi  4        548
BVI_COMMAND
-----------------------------------------------------
bvi -b 4489216 -s 8192 /mnt/ramdisk/book/users01.dbf

--//執行如上命令,修改資料塊其中一條記錄編碼 c24a23 => c248eb.
$ bvi -b 4489216 -s 8192 /mnt/ramdisk/book/users01.dbf
..
00449FB0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FC0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FD0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FE0  00 00 00 00 00 00 00 00 2C 02 02 02 C1 03 03 C2 ........,.......
00449FF0  4A 23 2C 01 02 02 C1 02 03 C2 48 EB 02 06 31 3B J#,.......H...1;
                                     ~~~~~~~~
0044A000

--//下劃線處就是修改後內容.注意要在bbed下執行sum apply更新檢查和.

BBED> set dba 4,548
        DBA             0x01000224 (16777764 4,548)

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 4, Block 548:
current = 0xd0bc, required = 0xd0bc

3.透過sqlplus查詢:
SCOTT@book> select rowid,t.*,dump(a,16) c30  from t;
ROWID                      ID          A C30
------------------ ---------- ---------- ------------------------------
AAAWLoAAEAAAAIkAAA          1            Typ=2 Len=3: c2,48,eb
AAAWLoAAEAAAAIkAAB          2       7334 Typ=2 Len=3: c2,4a,23

--//在toad下執行:
set linesize 160
column c30 format a30
select rowid,t.*,dump(a,16) c30  from t;

--//選擇execute as script按鈕,結果如下:

ROWID                      ID          A C30
------------------ ---------- ---------- ------------------------------
AAAWLoAAEAAAAIkAAA          1       7334 Typ=2 Len=3: c2,48,eb
AAAWLoAAEAAAAIkAAB          2       7334 Typ=2 Len=3: c2,4a,23
2 rows selected.

--//很明顯toad顯示出了問題.7334的整形編碼是c2,4a,23. pl/sql也一樣不再測試.
--//為什麼呢?
0x4a=74
0x23=35

--//oracle採用百位進位制,為了避開0 ,使用+1表示.這樣74=>73,35=>34 ,這樣表示7334無疑是正確的.

48=72
eb=235

--//我的理解或者(亂猜): 235= 2*100+35, 相當於 前面變成72+2=74 => 減少1對應73,剩下35 減少1,對應34,這樣解析為7334
--//按照道理toad工具不大可能解析整形,只能是相關的驅動出了問題.純粹亂猜,那位知道.

4.可以修改id=2的記錄:
--//135=0x87,如果修改c2 49 87 應該對應的整形也是7334,看看是否正確.

SCOTT@book> alter system flush BUFFER_CACHE;
System altered.

SCOTT@book> alter system checkpoint;
System altered.

$ bvi -b 4489216 -s 8192 /mnt/ramdisk/book/users01.dbf
..
00449F80  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449F90  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FA0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FB0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FC0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FD0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00449FE0  00 00 00 00 00 00 00 00 2C 02 02 02 C1 03 03 C2 ........,.......
00449FF0  49 87 2C 01 02 02 C1 02 03 C2 48 EB 01 06 31 3B I.,.......H...1;
          ~~~~~                      ~~~~~~~~
0044A000

--//下劃線處就是修改後內容.注意要在bbed下執行sum apply更新檢查和.

--//sqlplus 下查詢:

SCOTT@book> select rowid,t.*,dump(a,16) c30  from t;

ROWID                      ID          A C30
------------------ ---------- ---------- ------------------------------
AAAWLoAAEAAAAIkAAA          1            Typ=2 Len=3: c2,48,eb
AAAWLoAAEAAAAIkAAB          2            Typ=2 Len=3: c2,49,87

--//在toad下執行:
set linesize 160
column c30 format a30
select rowid,t.*,dump(a,16) c30  from t;

ROWID                      ID          A C30                          
------------------ ---------- ---------- ------------------------------
AAAWLoAAEAAAAIkAAA          1       7334 Typ=2 Len=3: c2,48,eb        
AAAWLoAAEAAAAIkAAB          2       7334 Typ=2 Len=3: c2,49,87        
2 rows selected.

--//這樣也驗證我的判斷.
--//我在測試中遇到出現壞塊的情況(也許忘記sum apply),我的dg自動修復了壞塊,我停止dg.再重複就演示相似的情況.

--//我在9.2.0.8 ,10.2.0.4 做了一點測試:
--//我在10g下查詢:
SCOTT@test> @ &r/conv_n c248eb
new   1: select utl_raw.cast_to_number(lower('c248eb')) n20 from dual
       N20
----------
      71bc

--//它能執行,11g報錯.

--//在9.2.0.8下:
08:20:57 sys@XXT1> select utl_raw.cast_to_number(lower('c248eb')) n20 from dual;

       N20
----------
      71`@
--//也能執行.

--//到底人為修改還是那個程式能錄入這樣效果,就不知道了.