Re: Number Data Type

On Feb 2, 9:07 am, "Ed Prochak" <edproc..._at_gmail.com> wrote:
> On Feb 2, 8:21 am, muralidhar..._at_hotmail.com wrote:
>
> > Hi All,
>
> > I am using Oracle 10g database.
> > I have a table with field name "Amount" and its datatype is
> > Number(7,2).
> > If I enter the amount value as 10.23, it is stored beautifully in the
> > database.
> > But, if I enter the amount value as 10.00, it is getting truncated and
> > stored in the databse as only 10 (The decimal part is not stored in
> > the database if both the digits in the decimal part are zeros')
>
> > Can any one help me in understanding this, and how to over come this?
>
> > Thanks in advance,
> > Murali.
>
> What makes you think the decimal part is not stored?
> 1 create table ejptest (
> 2 amount number(7,2)
> 3* )
> SQL> /
>
> Table created.
>
> SQL> insert into ejptest values (10.23);
>
> 1 row created.
>
> SQL> insert into ejptest values (10.00);
>
> 1 row created.
>
> SQL> select * from ejptest ;
>
> AMOUNT
> ----------
> 10.23
> 10
>
> SQL> column amount format 99999.99
> SQL> select * from ejptest ;
>
> AMOUNT
> ---------
> 10.23
> 10.00
>
> SQL>
>
> Don't confuse the display format with the storage format.
> Ed

Ed, is correct in that you can display the number any way you want by using the to_char function with a numeric format or in SQLPlus use the column command to format your output.

Oracle does not store leading or tailing zeroes in the internal number format as it is a form of scientific notation. How many leading zeroes, if any, and how many decical places you display on output are formatting considerations.

HTH -- Mark D Powell -- Received on Fri Feb 02 2007 - 09:14:28 CST