SQL & PL/SQL

Hi,

I'm on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

I am working on a project where have lots of view on a different schema. For performance reasons, we create tables on those views and index them.

The application that uses these tables requires a numeric primary key of a specific length, e.g. number(10). Not all tables have a natural key that matches this requirement, so I added a rownum to the query. I had hoped that casting the rownum to a number(10) would result in the same datatype once the table is created.

e.g.

SQL> create or replace view rownum_to_number10_vw as
  2  select cast(rownum as number(10)) objectid, dummy from dual;
 
View created
 
SQL> describe rownum_to_number10_vw;
Name     Type             Nullable Default Comments 
-------- ---------------- -------- ------- -------- 
OBJECTID NUMBER(10)       Y                         
DUMMY    VARCHAR2(1 BYTE) Y                         
 
SQL> 

perfect! Now create a table based on this view:

SQL> create table rownum_to_number10_tb as
  2  select * from rownum_to_number10_vw;
 
Table created
 
SQL> describe rownum_to_number10_tb;
Name     Type             Nullable Default Comments 
-------- ---------------- -------- ------- -------- 
OBJECTID NUMBER           Y                         
DUMMY    VARCHAR2(1 BYTE) Y                         

Oracle does not pick up on the number(10) cast!

How can I force Oracle to create a column with the same datetype as the underlying query?

ps:I know that the 10 in number(10) is more like a constraint than a datatype, but the application that uses this table will create an additional column if the datatype > 10. I want to prevent that from happening...

Thanks in advance!