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.