What are the common mistakes pl/sql programmers make with money computation?

Question

Hi there I am looking for common mistakes pl/sql programmers make when they program applications which deals with a lot of monetary computations. (Discounts, tax, rates, etc.) I had been using java and hibernate for monetary computations for as far as I can remember java has a set of rules and best practices like BigDecimal to preserve precision and etc. Right now Im trying to improve my pl/sql skills in dealing with financial modules thats why I want to know this gotchas and avoid them. Also are there any existing mantra or pl/sql best practice for this? Thanks in advance guys.

Answer 1

Using this example:

create table t_val
  (id number(10,0),
  value number(5,2));

declare
  v_dummy number;
begin
  delete from t_val;
  for i in 9 .. 20 loop
    insert into t_val values (i, 1/i);
    select count(*)
    into v_dummy 
    from t_val 
    where value = 1/i;
    dbms_output.put_line(to_char(i,'00')||':'||v_dummy||':'||
              to_char(1/i,'000.999999'));  
  end loop;
  --
end;
/

select id, value from t_val order by 1;

You can see that the code inserts, for example, 0.11111 which is implicitly rounded to 0.11 When the code immediately tries to count the values for 0.11111 it fails to find any matches.

Similarly the values for (1/14) and (1/15) both get rounded to 0.07.

This isn't a problem specific to PL/SQL, and I've seen similar issues in Java and PHP code when the value in the 'client' is rounded when it makes it into the database. PL/SQL does offer a better solution though, as you can declare a variable of the type of a specific table/column, and this binding is maintained even if the column is changed.

declare
  v_value  t_val.value%type;
  v_dummy number;
begin
  delete from t_val;
  for i in 9 .. 20 loop
    v_value := 1/i;
    insert into t_val values (i, v_value);
    select count(*)
    into v_dummy 
    from t_val 
    where value = v_value;
    dbms_output.put_line(to_char(i,'00')||':'||v_dummy||':'||
            to_char(1/i,'000.999999')||':'||to_char(v_value,'000.999999'));  
  end loop;
  --
end;
/

So best practice is, when dealing with an SQL statement, use bind variables that are anchored to the type (including length/scale/precision) of the underlying table.