Using APEX_COLLECTION to Manage Session State Working Tables

The Problem: Temporary Data in a Stateless World

Web applications are stateless by nature, but many workflows require accumulating data across multiple page views before committing it to the database. Think of a shopping cart, a multi step wizard, or a batch editing screen where the user builds up a set of changes and then saves them all at once. In Oracle APEX, APEX_COLLECTION provides an elegant solution for storing temporary, session scoped data without creating physical temporary tables.

How Collections Work

An APEX collection is a named set of rows stored in APEX’s internal session state tables. Each collection row has up to 50 VARCHAR2 attributes (C001 through C050), 5 NUMBER attributes (N001 through N005), 5 DATE attributes (D001 through D005), one CLOB attribute, one BLOB attribute, and an automatically assigned sequence ID. Collections are tied to the user’s APEX session and are automatically cleaned up when the session expires.

-- Create or reset a collection
BEGIN
    IF NOT APEX_COLLECTION.COLLECTION_EXISTS('CART_ITEMS') THEN
        APEX_COLLECTION.CREATE_COLLECTION('CART_ITEMS');
    ELSE
        APEX_COLLECTION.TRUNCATE_COLLECTION('CART_ITEMS');
    END IF;
END;
/

-- Add a member
APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => 'CART_ITEMS',
    p_c001            => 'PROD-12345',   -- product code
    p_c002            => 'Widget Pro',    -- product name
    p_n001            => 2,               -- quantity
    p_n002            => 49.99            -- unit price
);

Querying Collections

Collections can be queried through the APEX_COLLECTIONS view, which makes them usable as the source for reports, LOVs, and Interactive Grids:

SELECT seq_id,
       c001 AS product_code,
       c002 AS product_name,
       n001 AS quantity,
       n002 AS unit_price,
       n001 * n002 AS line_total
  FROM apex_collections
 WHERE collection_name = 'CART_ITEMS'
 ORDER BY seq_id;

Updating and Deleting Members

-- Update quantity for a specific row
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
    p_collection_name => 'CART_ITEMS',
    p_seq             => 3,
    p_attr_number     => 1,    -- N001 = quantity
    p_number_value    => 5
);

-- Delete a member
APEX_COLLECTION.DELETE_MEMBER(
    p_collection_name => 'CART_ITEMS',
    p_seq             => 3
);

When to Use Collections vs Other Approaches

Use APEX collections when you need temporary session scoped data that does not belong in a permanent table yet, when multiple pages need access to the same working set of data, or when you want to avoid creating and managing physical global temporary tables. Avoid collections when you need complex indexing or constraints on the temporary data, when the data volume per session is very large (collections are not optimized for thousands of rows per session), or when you need the data to survive beyond the APEX session.

For Interactive Grid based editing workflows, APEX 19.1 and later handle temporary row state internally, so collections are less necessary for simple grid editing. But for multi page wizards and complex accumulation patterns, APEX_COLLECTION remains the cleanest approach.