The Idea

apex_collections are a nightmare to use for storing large amount of items. “Was last_name column C023 or C032?” Permuting column names in read / insert / update commands on collections are a nightmare to find.

I would like to create views with proper column names on apex_collections and allow insert / update / delete via instead-of-triggers, that call the corresponding apex_collection.add/update/delete_member functions.

The Problem

For some reason the execution of "instead of triggers" for views on APEX_collections is not allowed. REVION-hotline says that this is a general restriction of Oracle APEX environment they cannot alleviate :-(

You can create the trigger, yet get an "ORA-01031: insufficient privileges" error when trying to execute the trigger. I cannot see any security reasons for this restriction, since you can modify APEX_collections via PL/SQL-packages provided by the Oracle APEX team only.

The Big Advantage

Views on apex_collections with insert/update/delete triggers would allow to use apex_collections like ordinary tables. Hence one could use all standard wizards and functionality of APEX for forms, reports, interactive grids etc. That would solve a lot of problems in complex apps and make code so much saver and easier to read.

For testing
Please run attached script to create a view and two instead-of-triggers for insert and update in any Oracle APEX account. At the end of the script you find a PL/SQL-block which tries to insert a record. The "instead of insert"-trigger does not reach the first statement dbms_output.put_line('Trying insert to ' || :new.collection_name); So it is a security restriction to execute the trigger.

create or replace view dm_vw_person_import as
select
    collection_name,
    seq_id as id,
    C001 as title,
    C002 as first_name,
    C003 as last_name
from APEX_collections;
/

create or replace trigger dm_vw_person_import_insert_trg
instead of insert on dm_vw_person_import
for each row
begin
    dbms_output.put_line('Trying insert to ' || :new.collection_name);
    apex_collection.add_member (
        p_collection_name => :new.collection_name,
        p_c001 => :new.title,
        p_c002 => :new.first_name,
        p_c003 => :new.last_name
    );
            
end dm_vw_person_import_insert_trg;
/

create or replace trigger dm_vw_person_import_update_trg
instead of update on dm_vw_person_import
for each row
begin
    apex_collection.update_member (
        p_collection_name => :new.collection_name,
        p_seq => :new.id,
        p_c001 => :new.title,
        p_c002 => :new.first_name,
        p_c003 => :new.last_name
    );
            
end dm_vw_person_import_update_trg;
/

-- Try to insert a record
declare
    v_rec dm_vw_person_import%rowtype;
begin
    -- create collection
    apex_collection.create_or_truncate_collection(
        p_collection_name => 'adr_collection'
    );


    v_rec.collection_name := 'adr_collection';
    v_rec.first_name := 'Hans';
    v_rec.last_name := 'Müller';

    insert into dm_vw_person_import values v_rec;
end;
/