Customizing DML in an APEX Interactive Grid

It should have been quite a relaxing Cricket World Cup final. After all, it was England v New Zealand. I was guaranteed to be on the winning side.
After several hours of nerve-shredding tension had failed to separate the teams England were awarded the trophy on the basis of dumb luck hitting more boundaries. The result was born with stoicism by the Black Caps, whose philosophy would, in other countries, be known as “Elite Niceness”. By a cruel twist of fate, Ben Stokes – England’s star all-rounder and Man of the Match – was actually born in Christchurch.
Oracle APEX has it’s own star all-rounder in the shape of the Editable Interactive Grid ( see what I did there ?)
As well as presenting information in the same way as an Interactive Report, it allows users to perform DML operations on the records it displays – provided it’s based on a single table.
What we’re going to look at here is how to base an Interactive Grid (IG) on a Query rather than a table whilst retaining the ability to perform DML operations on the displayed records. To achieve this, we’ll be customizing the PL/SQL that is executed when a DML operation is invoked in the IG.

The Application

For what follows, I’ll be using APEX 18.2 running against an Oracle 18cXE database.

We have two related tables which hold information about Men’s 50-over Cricket World Cup Finals :

The tables were created as follows :

create table teams(
    cid varchar2(3) primary key,
    team_name varchar2(100) not null)
/

create table finals(
    tournament_year number(4) primary key,
    date_played date,
    venue varchar2(100),
    winning_tcid varchar2(3) references teams(cid),
    losing_tcid varchar2(3) references teams(cid),
    winning_margin varchar2(100))
/    

… and have been populated with some data. The TEAMS table first…

insert into teams( cid, team_name)
values('AUS', 'AUSTRALIA');

insert into teams( cid, team_name)
values('ENG', 'ENGLAND');

insert into teams( cid, team_name)
values('RSA', 'SOUTH AFRICA');

insert into teams( cid, team_name)
values('WI', 'WEST INDIES');

insert into teams( cid, team_name)
values('IND', 'INDIA');

insert into teams( cid, team_name)
values('NZL', 'NEW ZEALAND');

insert into teams( cid, team_name)
values('PAK', 'PAKISTAN');

insert into teams( cid, team_name)
values('SL', 'SRI LANKA');

insert into teams( cid, team_name)
values('ZIM', 'ZIMBABWE');

insert into teams( cid, team_name)
values('BAN', 'BANGLADESH');

insert into teams( cid, team_name)
values('AFG', 'AFGHANISTAN');

insert into teams( cid, team_name)
values('IRL', 'IRELAND');

commit;

…and then FINALS…

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1975, to_date('21-JUN-1975', 'DD-MON-YYYY'), 'LORDS', 'WI', 'AUS', '17 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1979, to_date('23-JUN-1979' , 'DD-MON-YYYY'), 'LORDS', 'WI', 'ENG', '92 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1983, to_date('25-JUN-1983' , 'DD-MON-YYYY'), 'LORDS', 'IND', 'WI', '43 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1987, to_date('08-NOV-1987' , 'DD-MON-YYYY'), 'EDEN GARDENS', 'AUS', 'ENG', '7 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1992, to_date('25-MAR-1992' , 'DD-MON-YYYY'), null, 'PAK', 'ENG', '22 runs');

-- deliberate mistake to be corrected later
insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1997, to_date('17-MAR-1996' , 'DD-MON-YYYY'), 'QADDAFI STADIUM', 'SL', 'AUS', '8 wickets');

commit;

The data that we wish to present to application users can be retrieved with the following query :

select f.tournament_year, f.date_played, 
    initcap(f.venue) as venue,
    initcap(win.team_name) as winners,
    initcap(ru.team_name) as runners_up,
    f.winning_margin
from finals f
inner join teams win on f.winning_tcid = win.cid
inner join teams ru on f.losing_tcid = ru.cid
/

There are a couple of issues with the data as it stands so we want users to be able to edit the existing application records and add new ones.
As we’re using APEX, it would be good if we could use an Editable Interactive Grid as this would mean only needing to write a single page to handle all of these actions.
Of course, we could simply create a view using this query and then knock-up an Instead Of trigger to handle any DML. Alternatively…

Creating the Interactive Grid

The first step is to create a Region…

…and define it as an IG…

…using the above query as the Source SQL Query

When we run this (after saving our changes), we can see that the ROW_SELECTOR and ROW_ACTION widgets are missing :

Making the IG Editable

In order to persuade APEX to add these widgets, we need to make the IG Editable. We can do this by going to the Region’s Attributes and setting the Edit Enabled property to Yes

Among other things, this automatically creates a Save Interactive Grid Data process :

However, if we attempt to run the page now (after saving these changes), we’ll hit an error…

…so we need to select a Primary Key.
TOURNAMENT_YEAR will fulfil this purpose in our IG, so we just need to adjust the properties of the column :

When we save the change and run the page we can see that the ROW_SELECTOR and ROW_ACTION are now present :

The IG is not based on a table or view, remember, so we still need to tell APEX what to do when any DML actions are initiated by the user.

Customising the Save Interactive Grid Data process

Returning to the Processing Tab in the Page Designer we need to change the Type of this process to PL/SQL Code :

In the Source PL/SQL Code box, we need to enter a PL/SQL block which will be run whenever the Page processes a DML action.
In order to tell what specific DML action a row is subject to, we can look at the value of the built-in $APEXROW_STATUS variable.
The possible values are :

  • C – for Create
  • U – Update
  • D – Delete

Therefore, one approach for our PL/SQL block would be simply to include the actual DML statements we want to execute in-line like this :

declare 
    l_win_tcid finals.winning_tcid%type;
    l_ru_tcid finals.losing_tcid%type;
    
    cursor c_team_cid( i_name teams.team_name%type)
    is
        select cid
        from teams
        where team_name = upper(i_name);
begin
    -- reset the variables for each pass through this process
    l_win_tcid := null;
    l_ru_tcid := null;
    
    if :APEX$ROW_STATUS = 'D' then
        -- DELETE the record
        delete from finals
        where tournament_year = :TOURNAMENT_YEAR;
        
    else

        -- As we're either doing an UPDATE or an INSERT, we need to find the 
        -- CID value for each of the team names ( if specified)

        if :WINNERS is not null then
            open c_team_cid(:WINNERS);
            fetch c_team_cid into l_win_tcid;
            close c_team_cid;
        end if;

        if :RUNNERS_UP is not null then
            open c_team_cid(:RUNNERS_UP);
            fetch c_team_cid into l_ru_tcid;
            close c_team_cid;
        end if;
        
        if :APEX$ROW_STATUS = 'U' then
            -- UPDATE the record
            -- Note that, although DATE_PLAYED is a DATE field, the bind variable
            -- contains a string so we need to handle the conversion to a date here
            update finals
            set date_played = nvl(to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')), date_played),
                venue = nvl(:VENUE, venue),
                winning_tcid = nvl(l_win_tcid, winning_tcid),
                losing_tcid = nvl(l_ru_tcid, losing_tcid),
                winning_margin = nvl(:WINNING_MARGIN, winning_margin)
            where tournament_year = :TOURNAMENT_YEAR;
        
        elsif :APEX$ROW_STATUS = 'C' then
            -- CREATE (INSERT) as new record
            -- We need to return the Primary Key of the new record as APEX will
            -- use it to refresh the IG display and show the newly created row
            insert into finals( tournament_year, date_played, venue, 
                winning_tcid, losing_tcid, winning_margin)
            values( :TOURNAMENT_YEAR, to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')), :VENUE,
                l_win_tcid, l_ru_tcid, :WINNING_MARGIN)
            returning tournament_year into :TOURNAMENT_YEAR;
        end if;
    end if;
end;

The main points to note are :

  • The possible values of APEX$ROW_STATUS
  • the bind variables for the IG column values return a string, hence the explicit date conversion of :DATE_PLAYED
  • when the insert code generates a new primary key value (e.g. an ID taken from a sequence), APEX needs to know what it is so that it can display the new record once processing is completed.

On that last point, I believe that we may not need to return the PK value in this case because we’re already providing it explicitly in the UI. I’ve left it here as an illustration that this may be the case in other circumstances.

Anyhow, let’s give this a test.
First of all, we’re going to update the 1992 record with the venue :

The record for 1997 is wrong. The Tournament actually took place in 1996, so we’ll create a corrected record and delete the incorrect one :

In both cases, we get a success message :

…and if we check in the database, we can see that the DML has worked as expected :

NOTE : for any cricketing pedants reading – yes, I know that Sri Lanka won by 7 wickets, not 8. I’ll be correcting this when I “notice it” in a few paragraphs time.

At this point you may be less than thrilled at the prospect of having to maintain large chunks of PL/SQL in your APEX application.
Luckily for you, as the process code is a PL/SQL block, you can do pretty much anything you like…

Calling Stored Program Units from our APEX process

I’ve created a database package to handle DML operations on the FINALS table :

create or replace package edit_finals as

    procedure save_final( 
        i_year in finals.tournament_year%type, 
        i_date in finals.date_played%type,
        i_venue in finals.venue%type default null,
        i_winners in teams.team_name%type default null,
        i_losers in teams.team_name%type default null,
        i_margin in finals.winning_margin%type default null,
        o_year out finals.tournament_year%type);

    procedure delete_final( i_year in finals.tournament_year%type);
end edit_finals;
/

create or replace package body edit_finals as

    function get_team_cid( i_team in teams.team_name%type)
        return teams.cid%type 
    is 
        rtn_cid teams.cid%type;
    begin
        select cid 
        into rtn_cid
        from teams 
        where team_name = upper( i_team);

        return rtn_cid;
    end get_team_cid;

    procedure save_final( 
        i_year in finals.tournament_year%type, 
        i_date in finals.date_played%type,
        i_venue in finals.venue%type default null,
        i_winners in teams.team_name%type default null,
        i_losers in teams.team_name%type default null,
        i_margin in finals.winning_margin%type default null,
        o_year out finals.tournament_year%type)
    is 
        win_tcid teams.cid%type := null;
        ru_tcid teams.cid%type := null;
    begin
        if i_winners is not null then 
            win_tcid := get_team_cid( i_winners);
        end if;

        if i_losers is not null then 
            ru_tcid := get_team_cid( i_losers);
        end if;

        merge into finals 
            using dual 
            on ( tournament_year = i_year)
        when matched then update
            set date_played = nvl(i_date, date_played),
                venue = nvl(i_venue, venue),
                winning_tcid = nvl(win_tcid, winning_tcid),
                losing_tcid = nvl(ru_tcid, losing_tcid),
                winning_margin = nvl(i_margin, winning_margin)
            where tournament_year = i_year
        when not matched then 
            insert( tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
            values( i_year, i_date, i_venue, win_tcid, ru_tcid, i_margin);

        o_year := i_year;
    end save_final;

    procedure delete_final( i_year in finals.tournament_year%type) is 
    begin
        delete from finals
        where tournament_year = i_year;
    end delete_final;
end edit_finals;
/

This means that the PL/SQL code for the Save IG Process is a bit more compact :

begin
    if :APEX$ROW_STATUS = 'D' then
        edit_finals.delete_final(i_year => :TOURNAMENT_YEAR);
    elsif :APEX$ROW_STATUS in ('C', 'U') then
        edit_finals.save_final( 
           i_year => :TOURNAMENT_YEAR, 
           i_date => to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')),
           i_venue => :VENUE,
           i_winners => :WINNERS,
           i_losers => :RUNNERS_UP,
           i_margin => :WINNING_MARGIN,
           o_year => :TOURNAMENT_YEAR);
   end if;
end;

The behaviour is the same…

…which we can confirm in the database…

The benefit of this approach is that, if you decide to migrate from APEX to another front-end technology, the Package is there in the database and does not need to change.

References

I’ve not managed to find too much else out there on this topic, hence this post.
There is a very good forum post by Patrick Wolf.
This Oracle Tips and Tricks article may be worth a read.
Finally, there’s this rather comprehensive look at Interactive Grids by John Snyders.

1 thought on “Customizing DML in an APEX Interactive Grid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.