From Stew's comments on this post, I've been changing how I'm building applications at the office. Initially, I was creating a view and pairing it with a package which encapsulated the insert/update/delete logic, and replacing APEX's data processing procedures with calls to my custom package. Effective, but not exactly obvious, or as simple as I'd like. I like the new process better, but it does have one (rather significant) gotcha that bit me yesterday.
The new process still uses views and packages, but I'm now creating “instead of” triggers on the views to call the appropriate procedures in the package. This allows me to keep APEX's procedures, so I can build a page and have it work “out of the box” (even tabular forms!). Very nice. A major side benefit is that I can change which columns are used by the insert/update/delete procedures without having to go in and edit all of the pages where data can be touched; I just need to update the appropriate trigger(s) and I'm done.
For the curious, here's a sample (heavily redacted) update trigger:
create or replace trigger a112_data_v_upd_trg instead of update on a112_data_v referencing new as new old as old for each row declare begin a112_pkg.upd_data ( :new.primary_key_col, :new.data_col1, :new.data_col2, :new.data_col3, :new.data_col4, :new.data_col5, :new.md5 ); end a112_data_v_upd_trg;
Pretty basic; you could do more if you want, but I've got all the logic already in the package, so there's nothing else for the trigger to do.
Oh, yeah, there's a gotcha to all of this. APEX's Automatic Row Processing (DML) process tries to lock the records before updating, using “SELECT…FOR UPDATE”. And this doesn't work for complex views; your application will throw the following error:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Initially, this confused me, because I had FOR UPDATE in my package–just not in the procedure the trigger was calling. But then I found this post by Patrick Wolf which explains what's going on–this behavior was introduced in APEX 3.0–and, more importantly, gives a workaround. Which still works in APEX 4.0, thankfully. (Hey, Patrick, any chance we could get a “Don't use locking” checkbox added to the DML process, rather than having to go off and create application items? Just a thought!)