Round Number Datatypes in PLSQL (Doc ID 1272404.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal


=== ODM Question ===

The result of the following calculation 9000 * (1 + 9.5 * 7 / 36000)
is 9016,625.
When saving this result in a number datatype without precision and scale
Oracle rounds this to 9016,62499999999999999999999999999999998.

declare
    Num1 number;
begin
    Num1 := 9000 * (1 + 9.5 * 7 / 36000);
    dbms_output.put_line(Num1);
end;
/
9016,62499999999999999999999999999999998

When saving it in number with precision and scale then the result is:

declare
    Num1 number(38,15);
begin
    Num1 := 9000 * (1 + 9.5 * 7 / 36000);
    dbms_output.put_line(Num1);
end;
/
9016,625

With SQL the result is:

select 9000 * (1 + 9.5 * 7 / 36000) from dual;

9000*(1+9.5*7/36000)
9016,625

The problem is that when the result of 9000 * (1 + 9.5 * 7 / 36000)
will be round -> round(result,2)
9016,62499999999999999999999999999999998 => 9016,62
9016,625 => 9016,63

Why PLSQL rounds the result in NUMBER without precision and scale?


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms