[20171220]toad Plsql shows the bug.txt of shaping

[20171220]toad Plsql shows the bug.txt of shaping

--//afternoon have itpub netizens response, a query in Sqlplus,pl/sql under different. Links are as follows:
--//http://www.itpub.net/thread-2095697-1-1.html
--//I test the feeling that the data is wrong. Direct that its data test to see.

1. Environment:
[Email protected]> @ &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

[Email protected]> 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;

[Email protected]> 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

--//normal 7334 code is c24a23.

[Email protected]> @ &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

--//and the other side see the code is C248EB, obviously wrong.
[Email protected]> @ &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

[Email protected]> @ &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;

[Email protected]> alter system checkpoint;
System altered.

[Email protected]> alter system flush Buffer_cache;
System altered.

2. Modify the data block by bbed:
[Email protected]> @ &R/BBVI 4 548
Bvi_command
-----------------------------------------------------
Bvi-b 4489216-s 8192/mnt/ramdisk/book/users01.dbf

--//EXECUTE AS above command, modify data block one of the record codes 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 xx xx (2C) in the C1 of the C2 .....,.......
00449FF0 4 A 2C C1, C2-EB, Geneva, 3B J #,....... H... 1;
~~~~~~~~
0044a000

--//underline is the modified content. Note To perform the sum apply update check and in bbed.

Bbed> Set DBA 4,548
DBA 0x01000224 (16777764 4,548)

bbed> sum apply
Warning:contents of previous bifile'll be lost. Proceed? (y/n) Y
Check value for File 4, Block 548:
Current = 0XD0BC, required = 0XD0BC

3. Search by Sqlplus:
[Email protected]> 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


--//is executed under toad:
Set Linesize 160
Column C30 format A30
Select Rowid,t.*,dump (a,16) C30 from T;

--//Select the Execute AS Script button, the result is as follows:

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.

--//It was obvious that Toad showed a problem. 7334 of the Shaping code is c2,4a,23. PL/SQL is no longer tested.
Why is--//?
0x4a=74
0x23=35

--//oracle uses the hundred binary, in order to avoid 0, use +1 to indicate. So 74=>73,35=>34, that means 7334 is undoubtedly correct.

48=72
eb=235

--//my understanding or (random guess): 235 = 2*100+35, equivalent to the front becomes 72+2=74 = reduce 1 corresponds to 73, the remaining 35 is reduced by 1, corresponds to 34, this resolves to 7334
--//according to the Truth Toad tool is unlikely to resolve the shaping, can only be related to the driver out of the problem. Just guessing, the one who knows.

4. You can modify the id=2 record:
--//135=0x87, if modifying C2 49 87 should correspond to the shaping is also 7334, see if it is correct.

[Email protected]> alter system flush Buffer_cache;
System altered.

[Email protected]> 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 xx xx (2C) in the C1 of the C2 .....,.......
00449ff0 2C C1 at C2-EB, Geneva, and 3B.,....... H... 1;
~~~~~                      ~~~~~~~~
0044a000

--//underline is the modified content. Note To perform the sum apply update check and in bbed.

--//sqlplus under query:

[Email protected]> 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


--//is executed under 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.

--//This also validates my judgment.
--//I encountered a bad block in the test (perhaps forget sum apply), my DG automatically fixed the bad block, I stopped DG. Repeat to demonstrate similar situations.

--//I was in 9.2.0.8, 10.2.0.4 did a little testing:
--//I query under 10g:
[Email protected]> @ &r/conv_n C248eb
New 1:select Utl_raw.cast_to_number (lower (' C248eb ')) N20 from dual
N20
----------
71bc

--//it can execute, 11g error.

--//under the 9.2.0.8:
08:20:57 [Email protected]> select Utl_raw.cast_to_number (Lower (' C248eb ')) N20 from dual;

N20
----------
71 ' @
--//can also be executed.

--//in the end artificially modified or that program can input such effects, do not know.

[20171220]toad Plsql shows the bug.txt of shaping