Oracle Database Discussions

All,

I have a HUGE table that I'm "copying" using the create table as select functionality. There are timestamp datatypes in the original table that should be converted to number(16) in the new table.

I've written a function (returning number; defining the return value parameter to number(16)) to do the conversion, and upon creation of the new table (using CTAS) I send the timestamps through the function. The table creates successfully, but the datatype in the new table is just "number" not number(16). I can't modify the table and change the datatype because the column is not-null.

Because there is so much data and the new table has to be created quicky, I'm not sure what other options I have to change the number to number(16) in the newly created table. Can I explicitly define a number(16) in the CTAS clause to create the datatype to the precision I want?

Oracle 10.2.0.3.0
AIX 5.3