Re: float storage

On Jun 7, 10:05 am, Jeff Kish <jeff.k..._at_mro.com> wrote:
> <snip>
> I did notice that if I converted to number and then tried to
> convert to float, that I got this message:
>
> ORA-01440: column to be modified must be empty to decrease precision or scale
>
> which I guess implies that number has more capacity than float.
> Jeff Kish

Define 'capacity'. By default declaring a column as NUMBER generates a NUMBER(38) definition; there is no scale (decimal) to such a declaration. To increase the scale one must decrease the precision. A NUMBER(38) can also be specified as NUMBER(38,0); to provide greater scale one need define the column as NUMBER(X,Y) where Y is some nonzero  value less than or equal to X. Valid declarations would be:

NUMBER(38,9)
NUMBER(10,9)
NUMBER(4,3)
NUMBER(4,4)

Invalid specifications would be:

NUMBER(4,5) or any declaration where Y was greater than X. The table will create however the column can never be loaded due to:

ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

NUMBER() can be declared with or without a scale, as illustrated. The column being 'moved' from NUMBER to FLOAT requires that the precision be reduced to allow for additional scale; that the existing data may meet the maximum precision declared for the current definition is why you can't simply perform an ALTER TABLE to enact this change on a populated column.

The documentation explains this.

David Fitzjarrell Received on Thu Jun 07 2007 - 10:56:42 CDT