CODEWITHSUNDEEP
oracle-databaseplsql
anudeepks
anudeepks

Reputation: 1132

Oracle "Number" datatype precision

We are trying to store the values in a table which has a column of "Number" Datatype. The problem arises when we store a very small value , like "0.000001.

SQL> desc testing
Name Type   Nullable Default Comments 
---- ------ -------- ------- -------- 
A    NUMBER Y     

SQL> insert into testing values (0.00000001);
1 row inserted

SQL> select * from testing;
         A
----------
     0.001
      1E-5
      1E-8
    0.0001

Is there a way, we can store and retrieve the absolute values, as in store, 0.00001 instead of 1E-5.

Upvotes: 0

Views: 540

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

It is simply a DISPLAY issue. Set numformat properly.

For example,

SQL> create table t(a number);

Table created.

SQL> insert into t values(0.000000000000001);

1 row created.

SQL> select * from t;

         A
----------
1.0000E-15

SQL> set numformat 9D9999999999999999
SQL> select * from t;

                  A
-------------------
  .0000000000000010

SQL>

Update OP says the above didn't fix the issue when trying to send the number value to the application frontend. There must be something wrong with the locale-specific NLS settings that is being used by the application.

If the issue is with just the display, then you could convert it to char using to_char and proper format model.

SQL> create table t(a number);

Table created.

SQL> insert into t values(.000000000000000000001);

1 row created.

SQL> select * from t;

         A
----------
1.0000E-21

SQL> select ltrim(to_char(a, '9999999D999999999999999999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')) num from t

NUM
------------------------------
.000000000000000000001

SQL>

Note the use of ltrim.

Upvotes: 3

Related Questions