Re: best way to increase precision on a number column(s)?

That was exactly it Ray, I realized when I had created a copy of the table with a 5,3 precision for the columns in question and it let me do a insert into <copy_of_table> select * from <table> but would not let me change the precision of the column to number(5,3) on the table itself.

It turns out that when the database was designed it was given number(3), even though there was only 2 digit numbers in the column. I am assuming that Oracle must pad the numbers with a leading 0?, which would explain me not being able to change the column definition. When I changed it to number(6,3) it worked, problem solved!

"Ray Teale" <ray_at_BLAHholly.com.au> wrote in message news:zqp_7.3037$lt4.144647_at_ozemail.com.au...
>
> "Glen A Stromquist" <stromqgl_at_nospa_alpac.ca> wrote in message
> news:Mkk_7.6974$e4.1757404_at_news0.telusplanet.net...
> > I need to change the column precision in about 8 tables from a number
(x)
> to
> > number (x,3).
> >
> > What is the best / safest way to go about this?
> >
> > The tables have one or two primary keys, and some have referential
> > constraints as well.
> >
> > I am not decreasing the precision, but it still wont let me alter the
> table
> > as there is data in it.
> >
>
> You should still be able to modify the column with an alter table command.
> Are you sure you have the correct syntax?
> It may seem obvious, but many people forget that the number(3) must be
> changed to number(6,3) to have 999.999 format., number(3,3) as in your
> example reduces the precision.
>
> Kindest Regards
>
> Ray
>
>
>
> > Most of the tables have minimal data in them, excpet one with about
> 500,000
> > rows.
> >
> > Do I:
> >
> > create table table1_temp as select * from table1;
> > truncate table table1;
> > alter table table1 modify column_name number(3,3);
> > insert into table1 select * from table1_temp;
> > drop table1_temp;
> >
> > Or do I:
> >
> > rename table table1 to table1_temp;
> >
> > --modify then run the DDL from table1_temp to reflect the new column
> > precision and original table name
> >
> > insert into table1 select * from table1_temp;
> > drop table table1_temp;
> >
> > Or is there a better way that I havent thought of?
> >
> >
> > thanks in advance
> >
> >
> >
>
>
Received on Mon Jan 07 2002 - 16:59:37 CST