Money computations, comparison between java and Oracle/PL SQL which to use

Question

Guys can you give a good comparison regarding Oracle/PL SQL and Java in terms of their strengths and weaknesses when handling monetary computations. If you were to develop an application which will be handling a lot of money computations which of the two would you use and why?

This question is not to spark a debate between oracle/pl sql and java enthusiasts I just want to know what is considered the best practice or standard approach for this kind of requirement and the reason behind.

Scenario would be:

1. Data would be from the database (Oracle 10g minimum).
2. The program would compute and generate invoices based on aggregated data (100k-1M) records
3. Business rules are very complex
4. Business rules may change at least once a month
5. Several reference tables will be used in the computation
6. Program will be ran once a day

Thanks in advance.

Answer 1

These criteria definitely favour the use of PL/SQL.

1) Data would be from the database (Oracle 10g minimum).
2) The program would compute and generate invoices based on aggregated data (100k-1M) records
5) Several reference tables will be used in the computation
6) Program will be ran once a day

If it's in the database and involves data, especially lots of data, then PL/SQL is the default option.

The remaining criteria are trickier to evaluate:

3) Business rules are very complex
4) Business rules may change at least once a month

PL/SQL is definitely capable of undertaking computation, and it has lots of built-in arithmetic functions. So it comes down to exactly what you mean by "business rules" and "complex". We can code complex business logic in PL/SQL (I have done it) but Java has language features which undoubtedly make the task easier: I'm thinking of stuff like reflection and introspection.

Perhaps you are thinking about using a Rule Engine? Certainly, the financial services sector has been a major user of Rule Engines. And abstracting the rule sets from the processing would make it easier to satisfy the fourth criterion.

Java has two established Rule Engines, Drools and JESS. There is a Rule Engine in PL/SQL, and has been for some time, but it is mainly used as part of Oracle Streams. However, the API is exposed so it could be used for other purposes. Find out more.

If you're thinking about Rule Engines then I think that tips the hand towards Java. Business Rules Processing is a specialist programming paradigm, and there is simply more experience and support for it in Java than PL/SQL. I suspect that means going for Java in the middle tier rather than Java Stored Procedures, which has implications for network traffic and performance.

Answer 2

I think the most important aspect you should consider is "keeping your code DRY". You should at all costs avoid duplicating your complex business rules to both Java and PL/SQL. Having said this, you are likely to prefer putting most of it in PL/SQL, because then it will be available in procedures, views, etc when you actually need it.

In any case, for performance reasons, you are probably going to perform "data aggregation" in the database considering the complexity of tasks applied to a medium-sized amount of data. So if you actually need business rules for aggregation, they're already available in the database. No round-trip to Java is needed.