That happens with many numbers - it happens with all man made representation of numbers - it happens all of the time in computers (especially with floating point numbers - they are horrible)

(this isn't specific to Oracle, spreadsheets - all computer programs I've ever seen - everything is subject to this somewhere)

An Oracle number has up to 38 digits of precision. (A float has about 6 and a double about 13 - so the Oracle number is much more precise than those types)

The problem arises anytime in computer programs when you start doing things with really small numbers and really big numbers - we blow out the precision of the intermediate result - we cannot hold all of the digits.


The problem starts with 1/19. We cannot represent that number "perfectly"

ops$tkyte%ORA11GR2> select 1/19 from dual;

                                            1/19
------------------------------------------------
 0.052631578947368421052631578947368421052600000

(we run out of places to hold the decimal - that is not exactly 1/19, it is close). And when we multiply by 19 - we get into another bind - we have 38 digits of precision - but our numbers are so far apart - we'd need more than 38 to do it precisely so:

ops$tkyte%ORA11GR2> select 1/19*19 from dual;

                                         1/19*19
------------------------------------------------
 0.999999999999999999999999999999999999999400000

And now you see the issue. If we were to do this in floating point - it would work (with these numbers) by accident:

ops$tkyte%ORA11GR2> select 1f/19f from dual;

                                          1F/19F
------------------------------------------------
 0.052631579300000000000000000000000000000000000

<b>you see the massively reduced precision...</b>


ops$tkyte%ORA11GR2> select 0.0526315793f * 19f from dual;

                               0.0526315793F*19F
------------------------------------------------
 1.000000000000000000000000000000000000000000000

<b>and it appears to work - but only because of the reduced precision - for you see, using more "precise" numbers:</b>

ops$tkyte%ORA11GR2> select 0.0526315793 * 19 from dual;

                                 0.0526315793*19
------------------------------------------------
 1.000000006700000000000000000000000000000000000

<b>that isn't really 1, the error is actually much larger - but you lost it because you had a number with 11 digits to the right of the "." and a number with two digits to the left - the reduced precision arithmetic "lost" the error (by accident)

The only way I know "around it", is the old programming technique of not using "=", but using a tolerance. You cannot use another type of number (like a float or double) as they'll get errors as well - using different numbers.


instead of "if (x = 0) then"
you would "if (abs(x-0) < tolerance) then"

ops$tkyte%ORA11GR2> declare
  2          l_float binary_float;
  3          l_testf binary_float;
  4  
  5          l_double binary_float;
  6          l_testd  binary_float;
  7  begin
  8          for i in 1 .. 100
  9          loop
 10                  l_testf := i;
 11                  l_float := 1f/l_testf*l_testf-1f;
 12                  if ( l_float <> 0f ) then dbms_output.put_line( i|| ') l_float <> 0' );  end if;
 13  
 14                  l_testd := i;
 15                  l_double := 1d/l_testd*l_testd-1d;
 16                  if ( l_double <> 0d ) then dbms_output.put_line( i|| ') l_double <> 0' );  end if;
 17          end loop;
 18  end;
 19  /
41) l_float <> 0
47) l_float <> 0
49) l_double <> 0
55) l_float <> 0
61) l_float <> 0
82) l_float <> 0
83) l_float <> 0
94) l_float <> 0
97) l_float <> 0
98) l_double <> 0

PL/SQL procedure successfully completed.