Interactive grids have been around for a while now in APEX. I have found them extremely powerful and planning to have a few in production soon. I’m learning new things every day. This time I didn’t know how to change a row from editable to not editable. Probably very trivial for some, but that is always the case when you have done it before.

So how do control the DML behaviour of APEX?

Start by creating a table.

create table mytbl (mytbl_id number generated always 
                   as identity primary key,
                    somedata varchar2(1000),
                    status number);

Table MYTBL created.
rem Your user needs both create table and create sequence privileges

If the status column equals one then it’s editable, otherwise it should be “locked”.

rem Insert some data
insert into mytbl(somedata,status)
       values('I like Fridays',1);
1 row inserted.
commit;
Commit complete.

I never let APEX directly touch the tables. Using view or packages is the way to go. Let’s create a view that APEX can consume. From here we will inform APEX which row operations are permitted.

create or replace view myview
as
select somedata,
       status,
       case when status !=1 then 
        'locked'
       else
         'UD'
       end allowed_row_operation
from mytbl;

The value ‘UD’ tells APEX that an UPDATE or DELETE operation is allowed. Allowed values are

  • U – allow row update
  • D – allow row delete
  • UD – allow both Update and Delete

If anything else is returned the row will not be editable.

Now we have a view which we can use as a source for our Interactive Grid. After we have created the Interactive Grid, we must make it use our allowed_row_operation column. Under the attributes for the Interactive Grid, we can choose the column to control our DML behavior.

Now when this is applied APEX will just read the “flags” from the view column and behave accordinlgy.

Over&Out