Oracle APEX - DML on Collections

Introduction

APEX Collections are introduced in the Oracle APEX since... forever.

They provide a really nice replacement for Global Temporary Tables and can be very useful in a wide range of scenarios and use cases.

Data stored in APEX collections can be read via APEX_COLLECTIONS view and can be manipulated via APEX_COLLECTION API. 

But unfortunately Oracle APEX doesn't provide a functionality to execute DML statements on APEX collections and this is something I've been missing since... forever 😊

This means You can not execute the following statement successfully:

UPDATE apex_collections
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    collection_name = 'MY_COLL'
AND n001 > 50

You are going to get an error ORA-01031: insufficient privileges. 

In order to modify data in the collection You need to write a PL/SQL code instead, like this:

BEGIN
    FOR t IN (
        SELECT 
            seq_id,
            n001,
            c001,
            n002
        FROM apex_collections
        WHERE 
            collection_name = 'MY_COLL'
        AND n001 > 50
    ) LOOP
        apex_collection.update_member (
            p_collection_name => 'MY_COLL',
            p_seq => t.seq_id,
            p_c002 => t.c001,
            p_n002 => t.n002 * 2,
            p_d001 => trunc(sysdate + t.n001)
        );
    END LOOP;
END;

But fortunately, I found a way on how to execute DML statements on APEX collections!

Solution scripts and examples can be found in the GIT Hub repository here:

https://github.com/zorantica/db_apex_utils

The Idea behind the Solution

The idea behind the solution is to create a local updateable view in the target schema named "apex_collections_dml", on which You can execute DML statements.

This view contains an "instead of" trigger, which is triggered by DML operations and manages data in APEX collections via APEX_COLLECTION API.

The view is based on a pipelined function named "apex_collections_dml_pkg.get_coll_data", which simply returns data from APEX_COLLECTIONS view. The select statement / view source is 

SELECT * 
FROM 
    table (apex_collections_dml_pkg.get_coll_data)

This way a following UPDATE statement can be executed without ORA errors:

UPDATE apex_collections_dml
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    collection_name = 'MY_COLL'
AND n001 > 50

Why this Approach with the pipelined Function?

Well... the first idea was to create an instead of trigger directly on the APEX_COLLECTIONS view. But I have no privileges to do so. Plus, this also means messing up with APEX objects, which is never a good option. Plus, if You are on the cloud You have no access to APEX objects.

Second idea was to create a local view APEX_COLLECTIONS_DML, which reads data directly from APEX_COLLECTIONS view, and to create an instead of trigger on the view. But the problem was that the database is checking privileges on underlying objects BEFORE executing the trigger and therefore I ended up with "ORA-01031: insufficient privileges" error.

In order to avoid this ORA error I needed to somehow separate APEX_COLLECTIONS view object from my local DML view... and pipelined function was the solution I was looking for.

INSERT statement vs apex_collection.create_collection_from_query

The collection can be created in both ways and the SELECT statement, which prepares data is the same.

But... the SELECT statement used with the procedure apex_collection.create_collection_from_query is executed and validated during the runtime. And if the data structure used in the SELECT statement changes (for example some table is dropped or some columns are renamed) You are going to get errors during the runtime only.

On the other hand INSERT statement is validated during the development and compilation phase and program units with INSERT statement are going to be invalidated... as a good sign that something is not right...

Performance Concerns

I noticed no significant performance issues with this approach on the range of few hundreds or thousands of collection members.

Oracle APEX - beware of the "Quick Edit" button while editing the code in a popup editor!

I bumped into a problem with Oracle APEX App Builder while editing the page and been able to reproduce it on apex.oracle.com (current APEX version 23.2.4).

So, what happened was - the JavaScript code I was editing in the JavaScript popup editor overwrote item's label! This happened when I used the "Quick Edit" button in the running page and selected a page item with the editor still open.

Popup editors in the Page Designer are modal and this should prevent user from selecting another object while they are open... but with Quick Edit button this can be achieved and can lead to a problem if changes in the editor are accepted.

Other editors like PL/SQL editor or CSS editor behave in the similar way, but they are overwriting another item attributes... not necessarily the label. For example PL/SQL editor is overwriting text item's subtype, CSS editor is overwriting Template Options, File URL editor is is overwriting text item's Column attribute... HTML editor is overwriting the text item's label, similar to the JavaScript editor.

Beside those editors I mentioned, other editors might have the same behavior but I haven't tested them all.

Oracle "Instead of" triggers - beware of privileges and grants!

Recently I bumped into a problem while dealing with "instead of" triggers in the Oracle database.

You may read more about instead of triggers in the official documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html#GUID-9F06D45C-7C60-434E-A597-114A0C445671

The scenario is the following.

In my main schema (let's call it SC1) I created a view named DEMO_STATES_V based on the table DEMO_STATES located in another schema (let's call it SC2).

CREATE OR REPLACE VIEW my_demo_states AS
SELECT st, state_name
FROM sc2.demo_states;

The user SC1 has SELECT privilege granted on the table SC2.DEMO_STATES.

I created an "instead of" trigger on the view DEMO_STATES_V to handle DML operations. 

Currently there is only a dbms output but it is enough to present the problem.

CREATE OR REPLACE TRIGGER my_demo_states_trg
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON my_demo_states
FOR EACH ROW
BEGIN
    dbms_output.put_line('The trigger executes!');
END;
/

I tried to insert the record into the view and I'm expecting to get the dbms output written.

INSERT INTO my_demo_states (st, state_name)
VALUES ('SI', 'Slovenia');

But...

Instead of the trigger execution and dbms output produced I got an error!

ORA-01031: insufficient privileges

Seems that the database checked insert privileges BEFORE executing the trigger!

And while user SC1 does not have the insert privilege granted... an error occurred.

Honestly, I haven't expected that. I mean, if I have the "instead of" trigger created, why would database check any DML privileges on the underlying table? The "instead of" trigger is going to handle eventual DML operations... and grants should be checked for the PL/SQL code in the trigger body.

Another but... there is an override. 💪

If You modify the SELECT statement for the view object and implement UNION ALL from DUAL with "1=2" condition... so that actually no record is added to the original result set from the first part of the select statement... then everything works! For example:

CREATE OR REPLACE VIEW my_demo_states AS
SELECT st, state_name
FROM dome_ms1.demo_states
UNION ALL
SELECT null, null
FROM dual
WHERE 1=2;

No privileges are checked and the trigger executes flawlessly!