同样的建表语句，为什么9i会报错？

Question

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

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下却没问题。难道single table hash cluster是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>

Answer 1

我用的9201

估计这个大版本都有问题

Answer 2

估计蚊子老兄用的也是9204吧：）

Answer 3

已经找到原因，是bug，见Bug No. 3138341，cast函数在9204版本中使用不能返回正确的数据类型。

Answer 4

9i不支持吧

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  /

已创建数据簇。

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;

cluster hash_cluster(object_id)

                     *

ERROR 位于第 2 行:

ORA-01753: 列定义与聚簇列定义不兼容

SQL>

Answer 5

up～～～