APEX interactive grids & Invoke API with zero arguments

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.

If you are into classic passing of arguments, checkout this Invoke API article from Jon Dixon.

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.

Generated code

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;

MVC

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.

Snippets and Thoughts of One Oracle Developer

Search This Blog