APEX COLLECTIONS😎

Oracle Application Express (APEX) Collections are a powerful feature that enables developers to manage temporary, session-level data within their applications.

This Article discusses about below subjects:

• What Are APEX Collections?
• Why should we not use the Database's temp table instead of APEX Collections? 
• Structure of APEX Collections
• Lifecycle of an APEX Collection
• APEX Collection APIs
• When Should You Think About Using APEX Collections?

What Are APEX Collections?

Oracle APEX (Application Express) Collections are versatile, session-specific, in-memory data structures designed for temporary data storage and manipulation during a user's session. They function similarly to database tables but do not persist in the database, making them ideal for transient data needs. APEX Collections enable data sharing across pages within the same session, support efficient batch processing of large datasets, and allow data manipulation using PL/SQL procedures, making them valuable for multi-step forms and temporary data handling scenarios.

Why should we not use the Database's temp table instead of APEX Collections? 

maybe now you are thinking about this question. below article, there are some main points it

https://lidagholizadeh.blogspot.com/2024/10/why-should-we-not-use-databases-temp.html

Structure of APEX Collections

An APEX collection is essentially a table-like structure that consists of the following types of columns:

• C001 to C050: 50 columns available for storing custom string data (VARCHAR2).
• N001 to N005: 5 numeric columns for storing numeric data.
• D001 to D005: 5 columns for storing date values.
• Other Metadata: Includes the SEQ_ID (unique identifier for each row) and CREATED_ON (timestamp when the row was added).

These columns allow for flexibility in storing various types of data within a single collection.

Lifecycle of an APEX Collection

    1. Creation: Collections are created using the APEX_COLLECTION.CREATE_COLLECTION API, which initializes the collection in the current user session.

BEGIN APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'MY_COLLECTION', p_c001 => 'Val1', p_c002 => 'Val2', p_c003 => 'Val3' ); END;

    

    2. Manipulation: Data can be added, updated, or deleted using specific APIs like ADD_MEMBER, UPDATE_MEMBER, and DELETE_MEMBER.

BEGIN APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'MY_COLLECTION', p_c001 => 'Val1', p_c002 => 'Val2', p_c003 => 'Val3' ); END;

BEGIN
  APEX_COLLECTION.DELETE_MEMBER (
    p_collection_name => 'MY_COLLECTION',
    p_seq => 1  -- Row ID to delete
  );
END;

 👇delete an entire collection using the DELETE_COLLECTION procedure.

BEGIN
  APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => 'MY_COLLECTION'
  );
END;
 👇delete an entire collection using the DELETE_COLLECTION procedure.
BEGIN
  APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name => 'MY_COLLECTION'
  );
END;

    3. Persistence: Collections are temporary and exist only for the duration of the user session. Once the session ends, all collections associated with it are automatically cleared.

APEX Collection APIs

The APEX_COLLECTION package provides a robust set of procedures and functions to interact with collections. Below are some common APIs:

• CREATE_COLLECTION: Creates a new collection in the session.
• ADD_MEMBER: Adds a new row to an existing collection.
• UPDATE_MEMBER: Updates an existing row in a collection.
• DELETE_COLLECTION: Deletes the entire collection.
• DELETE_MEMBER: Deletes a specific row from the collection.
• COLLECTION_TO_JSON: Converts the collection's data to JSON format, useful for integration with external systems.