Why Transactional Database API approach?

We like Transactional Database API approach, because it:

1. make software components modular, I'm totally into modular programming
2. software modules must carry out a very specific task
(and be very efficient at carrying it out)
3. each software module should be loosely coupled (to limit dependencies)
4. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.
5. It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelihood of dodgy queries.
Profession = Efficiency + High Quality

Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

6. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed.The API implementation can be altered and tuned without affecting the client application.
7. The same APIs are available to all applications that access the database. No duplication of effort.
8. Eliminate SQL Parse in host language. Parse consume client host CPU and Server CPU and Latches. PL/SQL keep the SQL cursor cached and opened.
9. Eliminate data round trip; data type conversion, the context switch.
10. Tightly couple the data model and data process design, database world favor of Up Front Big Design.
Maximum the data share and reuse.
...
This list goes on and on.

Our concept is "build the data API in the database, you call the data API".
The data API encapsulate a transaction in a bit of code. Here we agree - no SQL in the client application, just call stored procedures - well tuned and developed explicitly to do that one thing.

Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.

On top of database schema we have an API that does the data stuff.
(generally, functions or Ref cursor to retrieve data, procedures to change data)

In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is - well, right where it belongs - next to the data, waiting for the 'next great programming paradigm to come along')

The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, Perl, PHP, Python, a mobile phone, <whatever the heck you want>.