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

No, I need to change the number so that if for example before it was 205.22 because of the decimal restraint of 2 places, after it could be 205.215232, but I need to keep all of the numbers currently in the Db.

Thanks,

Kelvin

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tffv7rkprqibae_at_beta-news.demon.nl...
>
> "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 - 10:22:20 CDT