Re: Changing the format of a Number column in an Oracle 8i Db

"Kelvin Jones" <kelvin_at_itxmarket.com> wrote in message news:9d8ufm$stm$1_at_reader1.fr.uu.net...
> Hi,
>
> I'm trying to modify a NUMBER column in a table from NUMBER(10,2) to
> NUMBER(10,6), changing from a number with 2 decimal places to a number
 with
> 6 decimal places.
>
> When I try this change I get the error msg:
> ORA-01440: column to be modified must be empty to decrease precision or
> scale
>
> ..however I'm not decreasing the precision. Maybe this is a common error,
> but I would be extremely grateful if somebody could give me a pointer as
 to
> how to change it, even if it's a round-about method.
>
> Many thanks,
>
> Kelvin
>
>

You are decreasing precision though.
The total number of digits is 10, in the previous situation there were two decimals, and now you want to change that to six, leaving four digits before the ,

Do you really want that?

If so,
the common tric for this is
create a table with whatever name
containing:
the primary key of the original table
the affected column
insert into new table
select primary key, affected column
from oldtable
update oldtable
set affected column = NULL
alter table oldtable
update oldtable x
set affect_column = (select affected column from newtable y where y.primary key = x.primay key) drop table newtable.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue May 08 2001 - 09:06:36 CDT