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.
Cheers, Paweł
2 comments:
My personal preference is for a mix of the two techniques.
I tend to favour your approach of auto-generation of a PL/SQL "wrapper" package for each table and/or business function, depending on the application.
But views are extremely attractive to provide isolated "windows" into a schema.
In fact, in some cases I've used views and "instead of" triggers as a way to transform a given schema into another.
For example, to evolve an underlying schema into a more normalized one, while keeping the previous access code the same.
Minimal intrusion was the goal in that case.
Anyways: just a few ideas.
Noons, thanks for your comment. I also use views, but I tend to not give direct access to views. I rather tend to return ref cursors that are in fact based on views. That way you have more control over your code and API functionality. And you control on database side how query is constructed, and how executed. Front-end developers usually have too superficial knowledge about database stuff.
Today Pete Finnigan has interesting entry on the same topis Using procedures to access data only. I agree with him, that dual access can be harder to control.
Post a Comment