Invoke API is available since APEX 22.2, since all my projects upgraded recently I can finally start using it. For me it was one of the greatest news introduced in APEX 22.2, not much invisible, but allowing me to shrink down Embedded Code report significantly.
I will be talking about a more minimalistic approach. I am a huge fan of storing logic in PL/SQL packages and views. This Invoke API feature allows me to just reference the procedure same way as I can reference view in grid, as a reference, not as a written code which need to be parsed and maintained. And this is not limited just for grids.
What I did in the past
- created view and assigned it to the grid, preferably via object reference (region source type = table/view)
- created procedure to handle the changes
- created caller to pass arguments from grid to the handler
- hoped that arguments wont change - I would have to change them in package body, spec and on the IG process - such a pain and waste of time
Compared to the normal way when you would just slapped your query to the region and wrote a handler directly on grid it was certainly a bit slower. Hence I wrote a generator which would acually generate this code for me and it worked fine, it saved me a lot of time and I could enjoy benefits of having code in database.
What I do now
- create view and assigned it to the grid via object reference
- create/generate procedure to handle the grid changes and assign it to grid via Invoke API
Just this. Isn't this awesome?
- passing zero arguments to handler means no sync is needed
- drastically reduced maintenance time, possible typos and bugs
- less code to copy paste around
Simple page example
Let's imagine that I have to build a page with a list of projects, page 300, this is what I would do:
- create a table tsk_projects
- create a view tsk_p300_projects_v which allows me to enrich data and control rows and columns
- create package tsk_p300 which will be dedicated for this page
- do any page computations and calculations in init_defaults procedure in this package (this procedure is called by application process on every page)
- generate tsk_tapi.projects procedure to store (create, update or delete) passed record in a table, fill in audit columns, log change...
- generate tsk_tapi.projects_d procedure to handle project removal (cascade delete)
- generate tsk_p300.save_projects procedure to create record from grid data and pass it to relevant TSK_TAPI procedure
- create a blank page 300
- add Hero region for header and future buttons, filters
- add IG region and choose view as a source
- make grid editable, rename process to SAVE_PROJECTS (to match procedure), change process to Invoke API and select TSK_P300 as a package, SAVE_PROJECTS as a procedure, neat
- add auth scheme to the region, to the Add/Update/Delete options in Attributes and to the IG process
- mark column(s) with primary key, check that all columns are sortable and with proper type, switch off encryption too
- open the page, adjust grid columns, save it as default
Any tips how long this takes? Well, lets say I have the table ready. Then I can do the rest in less then 2 minutes (yes, two minutes). Can you? You can, I will publish my new generator soon.
This is how the generated TAPI procedure looks like (I put this into TSK_TAPI package):
PROCEDURE projects ( rec IN OUT NOCOPY tsk_projects%ROWTYPE, -- in_action CHAR := NULL, in_client_id tsk_projects.client_id%TYPE := NULL, in_project_id tsk_projects.project_id%TYPE := NULL ) AS c_action CONSTANT CHAR := get_action(in_action); BEGIN -- evaluate access to this table tsk_auth.check_allowed_dml ( in_table_name => get_table_name(), in_action => c_action, in_user_id => core.get_user_id(), in_client_id => rec.project_id, in_project_id => rec.client_id ); -- delete record IF c_action = 'D' THEN tsk_tapi.projects_d ( in_client_id => NVL(in_client_id, rec.client_id), in_project_id => NVL(in_project_id, rec.project_id) ); -- RETURN; -- exit procedure END IF; -- are we renaming the primary key? IF c_action = 'U' AND in_project_id != rec.project_id THEN tsk_tapi.rename_primary_key ( in_column_name => 'PROJECT_ID', in_old_key => in_project_id, in_new_key => rec.project_id ); END IF; -- overwrite some values rec.updated_by := core.get_user_id(); rec.updated_at := SYSDATE; -- upsert record UPDATE tsk_projects t SET ROW = rec WHERE t.client_id = rec.client_id AND t.project_id = rec.project_id; -- IF SQL%ROWCOUNT = 0 THEN INSERT INTO tsk_projects VALUES rec; END IF; EXCEPTION WHEN core.app_exception THEN RAISE; WHEN OTHERS THEN core.raise_error(); END;
This is how the generated business procedure looks like (I put this into TSK_P300 package):
PROCEDURE save_projects AS rec tsk_projects%ROWTYPE; in_action CONSTANT CHAR := core.get_grid_action(); BEGIN -- change record in table rec.client_id := core.get_grid_data('CLIENT_ID'); rec.project_id := core.get_grid_data('PROJECT_ID'); rec.project_name := core.get_grid_data('PROJECT_NAME'); rec.is_active := core.get_grid_data('IS_ACTIVE'); -- tsk_tapi.projects (rec, in_action => in_action, in_client_id => NVL(core.get_grid_data('OLD_CLIENT_ID'), rec.client_id), in_project_id => NVL(core.get_grid_data('OLD_PROJECT_ID'), rec.project_id) ); -- IF in_action = 'D' THEN RETURN; -- exit this procedure END IF; -- update primary key back to APEX grid for proper row refresh core.set_grid_data('OLD_CLIENT_ID', rec.client_id); core.set_grid_data('OLD_PROJECT_ID', rec.project_id); EXCEPTION WHEN core.app_exception THEN RAISE; WHEN OTHERS THEN core.raise_error(); END;
So as you can see, for grids I do these things:
- create view and assigned it to the grid via object reference
- generate procedure to handle the grid changes and assign it to grid via Invoke API
- separate table storage logic in TAPI package, which allows me to isolate how are data stored from the business layer
- in business layer (typically TSK_P# package, TSK is app prefix, P# represents APEX page) I focus just on business process, not on the storage
- I can do pretty much whatever I want in the views, I just need to sync columns when they change
- I can have MVC in APEX :-)
Your application will change. And when it does then it really matters how you have build it. I am excited and confident in this MVC approach, it can handle everything with less effort than the traditional approach. I welcome changes now.
A table changes? Great, with a view on top my app wont break and I can use this view to secure what is visible and available to the users. View changes? No problem, just sync the region (and only when columns change). No need to sync/adjust grid handler in APEX anymore. Just open the relevant procedure and add column data in the record there. All changes protected by compilation time warnings, no more surprises when code breaks in Prod.
For many changes I don't even have to open APEX Builder, which also saves me a lot of time.