Best Practices Solutions: choose your moving parts carefully

Working with the Oracle eBusiness beast is a tad challenging given the breadth and complexity of its components. It uses a large majority of Oracle's core technology, both built and bought by Oracle. Integration throughout the technologies is fundamentally good and bound to get better with the whole Fusion soup. If there's something you need to provide a solution for, you have a myriad of options available to you without looking to that latest and greatest 3rd Party shiny thing. Or even that latest and greatest shiny thing within the tech stack.

The biggest workhorse with regards to language is PL/SQL but Java is making its presence felt. The number of Oracle Applications "Products" in use and available is increasing - both from a core technology stack perspective and an End User Application perspective.

This growth of moving parts leads to a necessity of choice:
What tool/language do I use given a custom development requirement?

Given my affinity to the intelligent or "thick" database paradigm, wherever possible I'll try to use the tool closest to the database.

What does this mean for me?
a) use SQL
b) use PL/SQL
c) use something else if you really have to

If you use SQL and PL/SQL wherever you can:

• Identification of performance issues should be easier due to the wealth of excellent tools at this level, e.g. AWR, statspack, dbms_profiler
• Integration issues should be easier to identify as you primarily have one place to look - the database
• The "disconnects" between components can be avoided (your session context is preserved)
• Code should be able to be consolidated to reduce complexity
• The number of product experts needed should be reduced
• Your licensing jigsaw puzzle should be simpler to arrange

Of course I'm over simplifying things here. There are situations where you can and must use another tool. However, the chances are that there will be something available to suit your needs in the Oracle eBusiness Suite Technology Stack. My point is even with additional tools, keep it simple and keep as much as possible in the database.

When you need to choose another tool, use the tool that:

• Is already in use performing a task similar to what you need to do
• Centralizes or consolidates the code and business rules
• Implements a simple rather than a complex solution

Lets take a few rules I use relating to other tools, with a caveat that there will be times when these rules are contradicted. I use these examples as the red flag in front of the eBusiness bull:

1. If you need to write a concurrent program, use PL/SQL
2. If you need to schedule something, use the Concurrent Manager calling your PL/SQL program
3. If you need a user interface, use the existing tech stack with business logic in the database wherever possible (I'll contradict myself upfront and say occasionally use Oracle APEX)
4. If you need to write a report*, use BI Publisher (BIP/XMLP) or Oracle Reports, with the business rules in PL/SQL
5. If you need an adhoc report*, use Discoverer
6. If you need to send an email, use PL/SQL and the Workflow Notification Mailer (Java Mailer) API

* = check existing reporting capabilities before making this decision - standard reports, FSGs, RX reports, etc.

Another point I want to make in this post is a quick reference to hardcoding. In the past I was a very strong advocate on avoiding hardcoding, but a number of recent experiences have softened my view on this. Now I'd recommend avoiding hardcoding only when necessary ... another one of those contentious issues, a read of this is recommended.

I can hear you saying "Alright, guv, where you going with this then, ay?".

Well, I'm writing this both as a point of reference and a point of reflection. Originally I wanted to include a comment on open source, but it just didn't surface.

Keep an eye out though ;-)

PS. I'm not an Oracle employee, so I get no direct benefit from the inherently Oracle bias to this post. I just like keeping my ducks in a row ;-)