Inaccurate result of an arithmetic operation when execute in procedure or function (oracle)

Question

I'm trying to calculate item affected on employee salary but the result of arithmetic operation is inaccurate as example return 99.99999999999999 instead of 100 . This happen only inside procedure or function but work correctly in case in separated sql query .

EX :

CREATE TABLE SS_TEST_CUT
(
  CUT  NUMBER
);

create or replace procedure ss_test (x1 number , x2 number , num out number) 
as 
x number ; 
begin 
    x := 100; 
    num := (x/30) *(30 - x1 - x2); 
    insert into ss_test_cut (cut) values (trunc (num,2));
end;

select cut from  ss_test_cut;

Actual result is : 99.99 Expected result : 100

Out side the procedure gives the expected result .

select (100/30 ) * 30 from dual; output : 100

why and how to avoid this with out round the number because other numbers have fraction part ? Thanks,

Answer 1

Edit: OK, now that the question is slightly clearer, the procedure is producing 99.9 recurring, which is not the same thing as 100, especially when you round down. However I still don't see PL/SQL giving an incorrect result where SQL is correct. As Bob's answer shows, 100 / 30 * 30 and (100 * 30) / 30 are two different things.

---

This is a display formatting issue (Edit: unless you round down...):

SQL> select 100 / 30 * 30 from dual;

 100/30*30
----------
       100

SQL> select to_char(100 / 30 * 30) from dual;

TO_CHAR(100/30*30)
----------------------------------------
99.9999999999999999999999999999999999999

99.9 recurring and 100 are effectively the same thing.

Here is another example to show that the result inside the procedure is the same as when using SQL directly:

SQL> create table demo (label varchar2(10), result number);

Table created.

SQL> insert into demo values ('SQL', 100 / 30 * 30);

1 row created.

SQL> declare
  2      num number;
  3  begin
  4      num := 100 / 30 * 30;
  5
  6      insert into demo values ('PL/SQL', num);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select label, result, to_char(result) from demo;

LABEL          RESULT TO_CHAR(RESULT)
---------- ---------- ----------------------------------------
SQL               100 99.9999999999999999999999999999999999999
PL/SQL            100 99.9999999999999999999999999999999999999

2 rows selected.

Answer 2

Re-order the operations in the calculation to avoid intermediate rounding:

create or replace procedure ss_test (x1 number , x2 number , num out number) 
as 
x number ; 
begin 
    x := 100; 
    num := (x * (30 - x1 - x2)) / 30; 
    insert into ss_test_cut (cut) values (trunc (num,2));
end;

Works as intended, assuming x1 and x2 are zero.

Best of luck.