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

In that case you need a number(14,6) in order to avoid to loose precision. And this change can be made without problem.

Hth,

Sybrand Bakker, Oracle DBA

"Kelvin Jones" <kelvin_at_itxmarket.com> wrote in message news:9d94ut$1mg$1_at_reader1.fr.uu.net...
> 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 - 11:52:59 CDT