CODEWITHSUNDEEP
oracle-database
user1016403
user1016403

Reputation: 12621

oracle truncates the value after saving an id with precisions?

I have a table. it has a column with datatype NUMBER(38,20). it is an id column. out application generates the id. i am trying to insert record with an id value of 105.00010. but it inserts only 105.0001. May i know the reason why it is truncating one value(0). it porperly inserts records from 105.00001 to 105.00009. while inserting 105.00010 it is truncating. Please help me.

column size is **NUMBER(38,20)**

Thanks!

Upvotes: 0

Views: 73

Answers (2)

Ollie
Ollie

Reputation: 17538

See the following test case:

WITH data_values
  AS (SELECT 105.0001 AS test_val FROM dual
      UNION ALL
      SELECT 105.00010 AS test_val FROM dual)
SELECT test_val,
       TO_NUMBER(test_val, '999.99999') AS NUM,
       TO_CHAR(test_val, '999.99999') AS STR
  FROM data_values;

Results in:

TEST_VAL NUM      STR
105.0001 105.0001 105.00010
105.0001 105.0001 105.00010

The value after the final non zero digit is irrelevent to Oracle. Both your numbers are equivalent.

Upvotes: 2

Tom Hubbard
Tom Hubbard

Reputation: 16139

The rightmost zeros after the decimal are insignificant, so the value is not truncated, it is still the same value.

If you need it to stay the same you may need to treat the value as a varchar2.

Upvotes: 2

Related Questions