In my approach PL/SQL API is the only possible way to retrieve and manipulate data in Oracle Database. Especialy when it goes for Web Applications.
- Logging user activity. Using triggers is fantasting to logging changes to data (Journaling), but usually is useless for logging user activity. User activity should be logged based on his/her actions and it’s parameters not data modifications. You do not want your batch processes to generate so many log.
- Performance Monitoring. With well-written API you can get very detailed statistics for time consumed by individual routine. Based on this statistics You can decide where are your bottlenecks and you can improve that.
- Simplicity. For easy, standard operations You should prepare generators that automatically generate API procedures for many simple tables.
- Cursors. PL/SQL procedures can return REF CURSORS as an output. With dynamic SQL you can return different variations of data depending on input parameters.
- API can be used for many Applications. In many big organizations one database is often accessed by different client applications. You do not have to write the same (similar) SQL’s in PHP, java or whatever other tool You want/plan to use.
If you are disagree or agree, please leave a comment.