Skip to Main Content
Feature Request FR-3990
Product Area Developer Experience
Status CLOSED

1 Voters

Automate APEX Collections CRUD operations for Forms and IGs

gerardotin Public · Sep 4 2024

Idea Summary
When dealing with APEX Collections and standard components (like Forms and Interactive Grids), DB operations have to be manually coded always.

If Oracle APEX could manage automatically the insert/update/delete operations on APEX Collection without manual coding, would speed some things up.

Use Case
A collection must be used to validate and store temporary data before inserting on definitive tables.
A page is created with an Interactive grid that has the following SQL:
“select collection_name, seq_id, c001, c002, c003 from apex_collections where collection_name = ‘DEMO_DATA’”
The edition is enabled on IG, and Automatic Row Process is created, and Target Type is set by default to “Region Source”.
There would be no need to change that, because APEX would detect automatically the collection and would use the APIs to manage collection data.

Preferred Solution (Optional)
Maybe new “Target Type” option for the collection.

For existing “Region source” type, I guess a possible (but not elegant) solution is a trigger on the APEX_COLLECTIONS view for insert, update and delete, invoking the correspondant APIs calls to APEX_COLLECTION package.

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Comments

Comments

  • fac586 OP 10 months ago

    For existing “Region source” type, I guess a possible (but not elegant) solution is a trigger on the APEX_COLLECTIONS view for insert, update and delete, invoking the correspondant APIs calls to APEX_COLLECTION package.

    This is not possible: see FR-2986.

    A page is created with an Interactive grid that has the following SQL:
    “select collection_name, seq_id, c001, c002, c003 from apex_collections where collection_name = ‘DEMO_DATA’”

    My experience is that this basic approach to use of collections rapidly becomes unworkable, with everyone becoming confused as to the purpose of arbitrarily named collection columns—especially when dealing with master/detail relationships and other situations in which joins are required across collections.

    The best approach is to create an abstraction layer at database level that hides the collection implementation behind read-only views (with logical column names) and transactional API packages.

  • gerardotin OP 10 months ago

    @fac586 The best approach is to create an abstraction layer at database level that hides the collection implementation behind read-only views (with logical column names) and transactional API packages.

    I agree, I'm thinking of SQL macros for this to be implemented in a standard way.