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.

Answer 2

Here are 2 quick hints:

The practical Oracle tip: use NUMBER (without scale/prec) both as table column type and in PL/SQL .. saves you alot headaches. NUMBER(x,y) does not save you any storage or cpu cycles ..

The general hint (you want to have the big picture first):

• First, research what your specific app requirements regarding decimal arithmetic really are: do you do tax? if yes, US or EU? rounding rules are different depending on jurisdiction and/or application. Does Oracle SQL support the desired stuff? Does PL/SQL? If not, the mistake is to use PL/SQL to do that stuff anyway.

• The upcoming gold standard for decimal arithmetic is IEEE decimal128. You may want to read on Wikipedia and probably http://www.carus-hannover.de/doc/DFP_PW6_in_SAP_NetWeaver_0907.pdf as an example. It supports all flavor of rounding etc. Don't know when it comes to Oracle QL / PL/SQL

Answer 3

The problem with dealing with MONEY types in any language is:

• Truncation - When you should have rounded but instead the result got truncated.
• Rounding - When you should have truncated, but it got rounded.

If you are careful about these at every step, handling MONEY is not such a tough job.