Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Number Data Type

Re: Number Data Type

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 02 Feb 2007 15:14:25 -0800
Message-ID: <1170458066.434309@bubbleator.drizzle.com>


muralidhargvn_at_hotmail.com wrote:

> On Feb 2, 8:14 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:

>> 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 --- Hide quoted text -
>>
>> - Show quoted text -
> 
> Ed,
> 
> Thank you for your suggestion.
> I understood that Oracle does not store leading or trailing zeroes in
> the internal number format.

There is no such thing as leading zeros if you are talking numbers. If you store a string '0000001' makes sense. With respect to trailing zeros are you saying one can not sure 1000 or one can not store 0.1000? In either case it is a matter of formatting the output ... not storing the data.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Feb 02 2007 - 17:14:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US