Why does Oracle round up a number with less than 38 significant digits?

Question

We have Oracle Server 10.2.

To test this, I have a very simple table.

CREATE TABLE MYSCHEMA.TESTNUMBER
(
  TESTNUMBER  NUMBER
)

When I try to insert 0.98692326671601283 the number gets rounded up.

INSERT INTO MYSCHEMA.TESTNUMBER (TESTNUMBER) 
VALUES (0.98692326671601283);

The select returns:

select * from TESTNUMBER

0.986923266716013

It rounds up the last 3 numbers "283" to "3".

Even looking at it with TOAD UI and trying to enter it with TOAD, I get the same result. Why? Is it possible to insert this number in an Oracle number without it getting rounded up?

Answer 1

I think you need to look into how your client program displays number values. An Oracle NUMBER should store that value with full precision; but the value may be rounded for display by the client.

For instance, using SQLPlus:

dev> create table dctest (x number);

Table created.

dev> insert into dctest VALUES (0.98692326671601283);

1 row created.

dev> select * from dctest;

         X
----------
.986923267

dev> column x format 0.000000000000000000000000000
dev> /

                             X
------------------------------
 0.986923266716012830000000000

As you can see, the default format shows only the first 9 significant digits. But when I explicitly change the column formatting (a client-side feature in SQLPlus), the full value inserted is displayed.