APEX Collections - When and How to Use Them with Real Examples

Oracle APEX Series · 9 min read · Intermediate → Advanced ✍️ Utkarsh Ranjan - Oracle APEX Cloud Developer Certified Professional (1Z0-771) ·

If you have been developing Oracle APEX applications for a while, you have likely hit a situation where you need to temporarily hold data - data that doesn't yet belong in a permanent database table.

Maybe it's a multi-step form. A shopping cart. A bulk upload preview. Or a list of rows the user is editing before a final save.

This is exactly where APEX Collections come in. They are one of the most powerful and underused features in the entire APEX framework - and once you understand them, you'll start seeing use cases everywhere.

In this article I'll walk you through:

• ✅ What APEX Collections are

• ✅ When to use them (and when NOT to)

• ✅ The complete API reference

• ✅ 2 real-world examples with full PL/SQL code

• ✅ Best practices and common pitfalls

Let's go.

What Are APEX Collections?

An APEX Collection is a named, session-specific temporary data structure - like an in-memory table that persists for the duration of a user's session.

It stores rows of data without touching your permanent database tables, and is automatically cleaned up when the session ends. Think of it as a scratch pad for your application.

It's stored in the APEX_COLLECTIONS view, backed by the internal WWV_FLOW_COLLECTIONS$ table.

Column Structure of Each Collection Row

💡 Key point: Collections are session-scoped. Two users can have a collection with the same name and their data stays completely isolated. No multi-user conflicts.

🗂️ When Should You Use APEX Collections?

The golden rule: use a collection when you need temporary, session-level storage that isn't ready to be committed to a permanent table yet.

🔧 The APEX_COLLECTION API - All Key Procedures

All operations go through the APEX_COLLECTION package:

💻 Real Examples with Complete Code

Example 1 — Order Staging with Interactive Report

The Use Case

In this example I built an Order Staging page where:

1. User clicks "Load Orders into Collection" button

2. All PENDING orders load into a Collection

3. Interactive Report displays the staged orders from the Collection

4. User reviews and clicks "Submit All Changes" to commit to database

Step 1 — The Demo Table

CREATE TABLE demo_orders (
    order_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(100),
    order_date    DATE DEFAULT SYSDATE,
    total_amount  NUMBER(10,2),
    status        VARCHAR2(20) DEFAULT 'PENDING'
);

INSERT INTO demo_orders (customer_name, total_amount, status)
VALUES ('Rahul Sharma', 15000, 'PENDING');
INSERT INTO demo_orders (customer_name, total_amount, status)
VALUES ('Priya Mehta', 28000, 'PENDING');
INSERT INTO demo_orders (customer_name, total_amount, status)
VALUES ('Amit Kumar', 9500, 'PENDING');
INSERT INTO demo_orders (customer_name, total_amount, status)
VALUES ('Sneha Singh', 42000, 'PENDING');
COMMIT;

Step 2 — Load Orders into Collection (Button Process)

This PL/SQL runs when user clicks "Load Orders into Collection" button:

Step 3 — Interactive Report SQL Source

Set this as the SQL Source of your Interactive Report region:

💡 The report reads directly from the collection - not from the database table. Users see exactly what's staged for commit.

Order Staging page running in the browser showing the Interactive Report with order data and both buttons visible (Load Orders + Submit All Changes)

Step 4 — Commit All to Database (Submit All Changes Button)

✅ Always DELETE_COLLECTION after a successful commit - prevents stale data if user revisits the page.

Both processes listed:

• "Load Orders to Collections" process

• "Commit all to database" process

How This Flow Works

User clicks "Load Orders"
        ↓
PL/SQL loops demo_orders
        ↓
Each row added to PENDING_ORDERS collection
        ↓
Interactive Report reads from apex_collections
        ↓
User reviews data on screen
        ↓
User clicks "Submit All Changes"
        ↓
PL/SQL loops collection → UPDATE demo_orders
        ↓
COMMIT → DELETE_COLLECTION

Example 2 — Employee Onboarding Wizard (3 Steps)

The Use Case

This is the most powerful Collections pattern. I built a 3-step Employee Onboarding Wizard:

• Step 1 — Basic Info: Full Name, Email, Department

• Step 2 — Job Details: Job Title, Salary, Start Date

• Step 3 — Confirm and Submit: Shows all data from Steps 1 & 2

Without Collections, data from Step 1 would be lost when navigating to Step 2. Collections act as the bridge.

WIZARD 1:

Step 1 — Page 4 "Next" Button Process

When user clicks Next on Step 1:

• Collection WIZARD_DATA is created

• Basic Info is stored as SEQ_ID = 1

• User moves to Step 2

WIZARRD 2:

Step 2 — Page 5 "Next" Button Process

When user clicks Next on Step 2:

• Job Details appended to existing collection

• Stored as SEQ_ID = 2

• User moves to Step 3

Step 3 — Page 6 Before Header Process

DECLARE
    l_row1  apex_collections%ROWTYPE;
    l_row2  apex_collections%ROWTYPE;
BEGIN
    IF APEX_COLLECTION.COLLECTION_EXISTS('WIZARD_DATA') THEN

        -- Read Step 1 data (Basic Info)
        SELECT * INTO l_row1
        FROM   apex_collections
        WHERE  collection_name = 'WIZARD_DATA'
        AND    seq_id = 1;

        -- Read Step 2 data (Job Details)
        SELECT * INTO l_row2
        FROM   apex_collections
        WHERE  collection_name = 'WIZARD_DATA'
        AND    seq_id = 2;

        -- Pre-fill Step 3 confirmation items
        :P6_CONFIRM_NAME   := l_row1.c001;   -- Full Name
        :P6_CONFIRM_EMAIL  := l_row1.c002;   -- Email
        :P6_CONFIRM_DEPT   := l_row1.c003;   -- Department
        :P6_CONFIRM_JOB    := l_row2.c001;   -- Job Title
        :P6_CONFIRM_SALARY := l_row2.c002;   -- Salary

    END IF;
END;

Before Step 3 loads:

• Reads SEQ_ID = 1 → fills Name, Email, Department

• Reads SEQ_ID = 2 → fills Job Title, Salary

• User sees ALL data from both previous steps ✅

• Progress bar showing ✅ Basic Info → ✅ Job Details → ● Confirm and Submit

How the Collection Flows Across Pages

Page 4 — Basic Info
    ↓ NEXT clicked
    WIZARD_DATA created
    ADD_MEMBER → SEQ_ID = 1
    (c001=Full Name, c002=Email, c003=Department)

Page 5 — Job Details
    ↓ NEXT clicked
    ADD_MEMBER → SEQ_ID = 2
    (c001=Job Title, c002=Salary, c003=Start Date)

Page 6 — Confirm and Submit
    Before Header runs
    SELECT SEQ_ID=1 → fills name, email, dept
    SELECT SEQ_ID=2 → fills job, salary
    User sees all data ✅

    FINISH clicked
    → Save to database
    → DELETE_COLLECTION('WIZARD_DATA')

💡 Why not just use session state? For simple wizards session state works fine. But when you have multiple rows per step (like adding multiple items), dynamic data, or complex staging logic - Collections give you a proper temporary table structure that session state cannot provide.

⚠️ 4 Rules to Always Follow

1. Use CREATE_OR_TRUNCATE not CREATE Calling CREATE_COLLECTION on an existing name throws an error. CREATE_OR_TRUNCATE_COLLECTION handles both cases safely.

2. Always check COLLECTION_EXISTS before reading Especially on Before Header processes — if user lands on Step 3 directly without going through Steps 1 and 2, the collection won't exist and your SELECT will fail.

3. Always DELETE_COLLECTION after commit Prevents stale data from appearing if user repeats the workflow in the same session.

4. Collections are NOT transactional If your DB commit fails and rolls back, the collection is NOT rolled back automatically. Handle cleanup in your EXCEPTION block.

Wrapping Up

Both examples above show the same core principle - stage first, commit later.

• Example 1 uses Collections to stage database rows for review before bulk commit

• Example 2 uses Collections to carry form data across multiple wizard pages

Once you get comfortable with these two patterns, you'll find Collections solving problems across almost every complex APEX workflow you build.

Follow me for more Oracle APEX, PL/SQL, HTML and CSS content.

OracleAPEX PLSQL LowCode APEXCollections OracleDatabase Tutorial

Utkarsh Ranjan · Oracle APEX Certified Pro (1Z0-771) ·