BUG：9204中cast不能返回正确的值

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/bug-can-not-return-right-datatype-when-use-cast/

问题是这样引起的：测试建一个single table hash cluster，建了好几次，也查了不少资料，一般报错 ORA-01753 是因为设置的字段类型不一致，但是经检查字段类型确实是一致了，且number也被cast定成了整形，应该没问题呀，但是总是报错：

test@ORADG(192.168.0.41)> create cluster hash_cluster
  2  (hash_key number(10))
  3  hashkeys 75000
  4  size 150
  5  single table
  6  hash is hash_key
  7  /
 
Cluster created.
 
Elapsed: 00:00:02.42
test@ORADG(192.168.0.41)> create table t_hashed
  2  cluster hash_cluster(object_id)
  3  as
  4  select owner,object_name,subobject_name,
  5  cast(object_id as number(10)) as object_id,
  6  data_object_id,object_type,created,
  7  last_ddl_time,timestamp,status,temporary,
  8  generated,secondary
  9  from all_objects;
cluster hash_cluster(object_id)
                     *
ERROR at line 2:
ORA-01753: column definition incompatible with clustered column definition
 
Elapsed: 00:00:00.10
test@ORADG(192.168.0.41)> select * from v$version;
 
BANNER
----------------------------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

但是同样的语句在10g中是可以实现的：

SQL> create cluster hash_cluster
  2  (hash_key number(10))
  3  hashkeys 75000
  4  size 150
  5  single table
  6  hash is hash_key
  7  /
 
Cluster created.
 
SQL> create table t_hashed
  2  cluster hash_cluster(object_id)
  3  as
  4  select owner,object_name,subobject_name,
  5  cast(object_id as number(10)) as object_id,
  6  data_object_id,object_type,created,
  7  last_ddl_time,timestamp,status,temporary,
  8  generated,secondary
  9  from all_objects;
 
Table created.
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL>

一开始是怀疑9i版本不支持single table hash cluster，但是测试直接写建表语句就能顺利建立：

test@ORADG(192.168.0.41)> create table t_hashed
  2  (owner VARCHAR2(30),
  3  object_name VARCHAR2(30),
  4  subobject_name VARCHAR2(30),
  5  OBJECT_ID       NUMBER(10,0),     
  6  DATA_OBJECT_ID  NUMBER,     
  7  OBJECT_TYPE     VARCHAR2(18),
  8  CREATED         DATE,       
  9  LAST_DDL_TIME   DATE,       
 10  TIMESTAMP       VARCHAR2(19),
 11  STATUS          VARCHAR2(7),
 12  TEMPORARY       VARCHAR2(1),
 13  GENERATED       VARCHAR2(1) ,
 14  SECONDARY       VARCHAR2(1)
 15  )
cluster hash_cluster(object_id) 16 
 17  /
 
Table created

因此进一步怀疑cast函数的问题，进一步测试：

####### 9204上 ###############
test@ORADG(192.168.0.41)> create table xx(a number);
 
Table created.
 
Elapsed: 00:00:00.09
test@ORADG(192.168.0.41)> create table yy as select cast(a as number(10,0)) as a from xx;
 
Table created.
 
Elapsed: 00:00:00.09
test@ORADG(192.168.0.41)>
test@ORADG(192.168.0.41)>
test@ORADG(192.168.0.41)> desc xx
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER
 
test@ORADG(192.168.0.41)> desc yy
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER
 
 
 
##### 10201上 #######################
SQL> create table xx(a number);
 
Table created.
 
SQL> create table yy as select cast(a as number(10,0)) as a from xx;
 
Table created.
 
SQL> desc xx;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 
SQL> desc yy
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(10)

看来确实是因为这个cast函数的问题了，查了一下，发现这个问题竟然是个BUG：Bug 3138341：

Subject:  Support Description of Bug 3138341
  Doc ID:  Note:3138341.8 Type:  PATCH
  Last Revision Date:  26-MAR-2004 Status:  PUBLISHED
 Click here for details of sections in this note.
 
Bug 3138341  Wrong datatypes returned from PLSQL REF Cursor using CAST
 This note gives a brief overview of bug 3138341.
 
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 10G 
Versions confirmed as being affected 8.1.7.4
9.0.1.4
9.2.0.4
 
Platforms affected Generic (all / most platforms affected)
 
Fixed:
This issue is fixed in 9.2.0.5 (Server Patch Set)
10g Production Base Release
 
 
Symptoms:
Wrong Results
Related To:
Datatypes - Objects (Types/Collections)
PL/SQL
Description
 
 
Calling a PLSQL stored procedure containing a REF cursor
on a select with a CAST operator on an ADT can return the
wrong precision value.
 
 
--------------------------------------------------------------------------------

· 【文章发布信息】发表于： 2008-01-20 @ 17:51:15 · ||分类： Study note

留条评论