Number(28,15) not accurate when viewed in PL/SQL Dev

[denmarkdev]

The data type Number(28,15) is sometimes unprecise when selected from PL/SQL developer.

-- Create table
create table NUMBERTEST
(
c1 number,
c2 NUMBER(28,15),
c3 NUMBER(28,10)
);

insert into NUMBERTEST select 495.96, 495.96, 495.96 from dual;

select c1, c2, c3
,TO_CHAR(c1,'FM99999999999999999999D99999999999999999999', 'NLS_NUMERIC_CHARACTERS = ''.,''') b1
,TO_CHAR(c2,'FM99999999999999999999D99999999999999999999', 'NLS_NUMERIC_CHARACTERS = ''.,''') b2
,TO_CHAR(c3,'FM99999999999999999999D99999999999999999999', 'NLS_NUMERIC_CHARACTERS = ''.,''') b3
from NUMBERTEST;

Result:

1 495.96 495.960000000000064 495.9600000000 495.96 495.96 495.96

Notice the number 495.960000000000064

 

[Marco Kalter]

This is a floating point precision issue. For the SQL Window you can keep to full precision from Oracle by using the "Number fields to_char" preference (Tools > Preferences > SQL Window).

 

[denmarkdev]

Hi Marco

That was also my guess. But how come there isn't any precision loss when using Number or Number(28,10)?

Is there any side effect by using "Number fields to_char"? If not, why isn't it checked by default?

 

[Marco Kalter]

That was also my guess. But how come there isn't any precision loss when using Number or Number(28,10)?

Floating point precision on the client is approximately 15 digits. It's a binary system, so some values with greater precision can exactly be represented, and some cannot.

Is there any side effect by using "Number fields to_char"? If not, why isn't it checked by default?

Things like sorting, aligning, validation, and aggregation do no longer work.

 

[denmarkdev]

Thanks.

 

[denmarkdev]

Any plans of using a big decimal class to avoid these issues?

Still I don't get why Number(28,10) and Number(28,15) gives different results? In Oracle it is exact 495.96. In booth cases PL/SQL Developer is casting it into a double. If the number cannot be represented in a double, then both should be unprecise. But Number and Number(28,10) is exact! Why?

 

[denmarkdev]

Marco, i really like to here you opinion on this.

 

[Marco Kalter]

Any plans of using a big decimal class to avoid these issues?

Yes, but it's a bit of work since precision is beyond normal math libraries.

Still I don't get why Number(28,10) and Number(28,15) gives different results? In Oracle it is exact 495.96. In booth cases PL/SQL Developer is casting it into a double. If the number cannot be represented in a double, then both should be unprecise. But Number and Number(28,10) is exact! Why?

I'm not sure why, but my guess is that the Oracle client library uses different conversion rules for different precisions.