Hi Tom,
We use the below rounding logic in our applications but unfortunately the first query was rounding incorrectly. This we can see only when this code was executed from the application. But when I execute it in sql*plus it giving me correct output.
Is there any db issues in rounding?
select round(16562.845/0.01) * 0.01 from dual;--16562.84 , It should round to 16562.85
select round(17145.945/0.01) * 0.01 from dual;--17145.95 , it is correctly rounding.
This due to the way that floating point arithmetic works. Real numbers are always stored as an *approximation* due to the limits of precision on computers.
For example, we can't store the *exact* result of 1/3
SQL> select 3*(1/3) x from dual;
X
----------
1
but if I ask for a higher level of precision, I see things like this
SQL> col x format 99.9999999999999999999999999999999999999999999999999999999999
SQL> select (1/3)*3 x from dual;
X
--------------------------------------------------------------
.9999999999999999999999999999999999999999000000000000000000
In your case, I would suspect that something like
16562.8449999999999999999999999...
is being sent back to the client, which is then rounded downwards.
You always need to take care when dealing with floating point arithmetic (not just in Oracle, but any language)