Re: Storage of NUMBER Type

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 06 Jan 1999 02:38:48 GMT
Message-ID: <3697cc61.2874152@192.86.155.100>

A copy of this was sent to "Lawrence" <lsimela_at_mahalini.prestel.co.uk> (if that email address didn't require changing) On Tue, 5 Jan 1999 22:53:58 -0000, you wrote:

>Everyone
>I would be most grateful if someone can explain the storage of the NUMBER
>data type in a database column. It may seem like a simple question but:
>
>A column defined as Number(9,2) stores 1234567.19 but one defined as
>Number(10,2) cannot store 12345678.12
>
>Unfortunately the manuals are not very clear on this.
>
>Thank you in advance
>
>Lawrence
>

I think you are seeing the data through sqlplus and are hitting the 'numformat' changing your displayed data. the number 12345678.12 is in the database, sqlplus is just rounding it for display. consider:

SQL> create table nums ( x92 number(9,2), x102 number(10,2) );

Table created.

SQL>
SQL> insert into nums values (1234567.19,12345678.12 );

1 row created.

SQL>
SQL> select * from nums;

X92 X102
---------- ----------
1234567.19 12345678.1

SQL> 
SQL> set numformat 999999999999999.99999
SQL> 
SQL> select * from nums;

                   X92                   X102
---------------------- ----------------------
         1234567.19000         12345678.12000

So, the number is there -- sqlplus was 'hiding' it from you.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 05 1999 - 20:38:48 CST