[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`@
--//也能执行.
--//到底人为修改还是那个程序能录入这样效果,就不知道了.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库