Collections in Oracle APEX

When I first started working with Oracle APEX, I struggled with handling temporary data, especially when building multi-step forms, shopping-cart-like functionality, or wizard-style pages. That’s when I discovered APEX Collections, a powerful yet often underused feature that makes session-based data management incredibly simple.

What Are Collections in Oracle APEX?

Oracle Application Express (APEX) provides a feature called Collections that allows developers to store temporary data in memory during a user session.

Think of a collection as:

A temporary, session-based table that exists only for the duration of the user’s session.

They are extremely useful when:

You need temporary storage
You want to stage data before committing to a real table
You’re building multi-step processes
You want to manipulate datasets dynamically

Understanding How Collections Work

Internally, collections are stored in:

WWV_FLOW_COLLECTIONS$

But you should never directly manipulate this table. Instead, you use the APEX_COLLECTION API.

Key characteristics:

Session-specific
User-specific
Stored in database session
Automatically cleared when session ends
Not committed permanently unless you copy data to real tables

Datatypes Supported in Collections

Each collection row can hold:

Character Attributes

C001 to C050 (50 VARCHAR2 columns)

Numeric Attributes

N001 to N005 (5 NUMBER columns)

Date Attributes

D001 to D005 (5 DATE columns)

Large Object

CLOB001

Binary Attribute

BLOB001

XMLTYPE Attribute

XMLTYPE001

MD5_ORIGINAL Attribute

Varchar of size of 4000 chars

So a collection row can hold structured data similar to a table row. And additionally it has following two columns:

COLLECTION_NAME NOT NULL VARCHAR2(255)
SEQ_ID NOT NULL NUMBER

by which you can identify any row easily.

NOTE: there are updates coming, so hopefully soon in newer versions of Apex we’ll get more number columns (compared to current limit of five, where if the need arises, you have to use character columns for numbers… not very effient, right?)

Creating a Collection

BEGIN
    APEX_COLLECTION.CREATE_COLLECTION(
        p_collection_name => 'EMP_COLLECTION'
    );
END;

Or safer version:

BEGIN
    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
        p_collection_name => 'EMP_COLLECTION'
    );
END;

This ensures the collection exists and is empty.

Inserting Data into Collection

BEGIN
    APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'EMP_COLLECTION',
        p_c001 => 'John',
        p_c002 => 'IT',
        p_n001 => 5000,
        p_d001 => SYSDATE
    );
END;

Here:

C001 = Employee Name
C002 = Department
N001 = Salary
D001 = Join Date

Querying Collection Data

SELECT
    c001 AS employee_name,
    c002 AS department,
    n001 AS salary,
    d001 AS join_date
FROM
    APEX_COLLECTIONS
WHERE
    collection_name = 'EMP_COLLECTION';

APEX_COLLECTIONS is a view used to access collection data.

Deleting a Single Member

Each row has a SEQ_ID.

BEGIN
    APEX_COLLECTION.DELETE_MEMBER(
        p_collection_name => 'EMP_COLLECTION',
        p_seq => 1
    );
END;

Truncating a Collection

BEGIN
    APEX_COLLECTION.TRUNCATE_COLLECTION(
        p_collection_name => 'EMP_COLLECTION'
    );
END;

Removes all members but keeps collection definition.

Deleting Entire Collection

BEGIN
    APEX_COLLECTION.DELETE_COLLECTION(
        p_collection_name => 'EMP_COLLECTION'
    );
END;

Completely removes it from session.

Session Persistence -> How Long Do Collections Live?

Collections:

Exist per user session
Persist across page navigations
Survive commits and rollbacks
Are automatically deleted when session expires

⚠️ Important:
They are NOT permanent storage.

If the user logs out or session times out → collection disappears.

Updating Collection Members

BEGIN
    APEX_COLLECTION.UPDATE_MEMBER(
        p_collection_name => 'EMP_COLLECTION',
        p_seq => 1,
        p_c001 => 'Michael',
        p_n001 => 7000
    );
END;

Checking If Collection Exists

SELECT APEX_COLLECTION.COLLECTION_EXISTS('EMP_COLLECTION')
FROM dual;

I found this useful to have on page load (if it doesn’t exist, then create it)

Loading Data from a Query

BEGIN
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
        p_collection_name => 'EMP_COLLECTION',
        p_query => 'SELECT ename, deptno, sal FROM emp'
    );
END;

This is powerful for staging real table data into a temporary structure.

Some examples of using collections:

1️. Wizard-Style Multi-Step Forms

Use collections to:

Store step 1 data
Store step 2 data
Validate everything
Insert to real tables only at final step

2️. Shopping Cart Implementation

Add products to collection
Update quantity
Calculate totals using SUM(n001)
Insert order when user checks out

3️. Bulk Processing

Instead of committing row by row:

Collect data in collection
Process in batch PL/SQL block
Commit once

4️. Complex Validations Before Insert

Use collection as:

Staging table before final commit

Validate:

Duplicates
Business rules
Cross-record validation

Performance Considerations

Collections are stored in database tables
Large collections may impact performance
Clean them when not needed
Avoid storing huge CLOBs unnecessarily

When Should You Use Collections?

Use Collections When:

✔ You need temporary storage
✔ Data is session-scoped
✔ Multi-step forms
✔ Staging before insert
✔ Complex validations

Avoid When:

✘ Data must be persistent
✘ Large-scale reporting
✘ Cross-session sharing required

Addition, using Apex collection with file uploads

Once I found myself I need to use Apex collections as temporary session storage, but I needed to upload a file as well (of course prior of commiting it to actual database table), and is there a way? There is, let me simplify it for you…

Note that you can use file upload to apex temporary file table without using collections, but since this blog post is about collections, let do it completely.

How File Upload Works in APEX

When you use a File Browse item in APEX:

User selects a file
APEX uploads it into a temporary table
The file is stored in:

APEX_APPLICATION_TEMP_FILES

This table holds the file for the duration of the session.

Structure of APEX_APPLICATION_TEMP_FILES

Important columns:

Column	Description
NAME	Internal APEX file reference
FILENAME	Original file name
MIME_TYPE	File content type
BLOB_CONTENT	Actual file data
CREATED_ON	Upload timestamp
CREATED_BY	User

This table is session-specific.

⚠️ Data is automatically cleaned up when the session ends. Just to note again 🙂

Basic Example -> Fetch Uploaded File

Step 1: Create File Browse Item

Create a page item:

P1_FILE
Type: File Browse
Storage Type: Table APEX_APPLICATION_TEMP_FILES

Step 2: Fetch File from Temporary Table

After submit:

DECLARE
    l_blob        BLOB;
    l_filename    VARCHAR2(255);
    l_mime_type   VARCHAR2(255);
BEGIN
    SELECT blob_content,
           filename,
           mime_type
    INTO   l_blob,
           l_filename,
           l_mime_type
    FROM   apex_application_temp_files
    WHERE  name = :P1_FILE;    -- Now you can insert into your own table
    INSERT INTO my_documents (
        file_name,
        mime_type,
        file_content,
        uploaded_on
    ) VALUES (
        l_filename,
        l_mime_type,
        l_blob,
        SYSDATE
    );END;

✅ This permanently stores the file.

Using Collections with File Uploads (Very Powerful)

Now let’s combine uploads with APEX Collections.

Common use case:

Upload multiple files → validate → show preview → commit later

Store Uploaded File in Collection

BEGIN
    FOR rec IN (
        SELECT filename,
               mime_type,
               blob_content
        FROM   apex_application_temp_files
        WHERE  name = :P1_FILE
    )
    LOOP
        APEX_COLLECTION.ADD_MEMBER(
            p_collection_name => 'FILE_UPLOAD_COLLECTION',
            p_c001            => rec.filename,
            p_c002            => rec.mime_type,
            p_blob001         => rec.blob_content
        );
    END LOOP;
END;

Now the file is stored in a session-based collection instead of directly committing to DB.

Display Files from Collection

SELECT
    seq_id,
    c001 AS file_name,
    c002 AS mime_type
FROM
    apex_collections
WHERE
    collection_name = 'FILE_UPLOAD_COLLECTION';

You can create an Interactive Report on this query. (and even functionality to download the file while in collection -> download blob type)

Cleaning Up Temporary Files

After processing, you may explicitly remove temp files:

DELETE FROM apex_application_temp_files
WHERE name = :P1_FILE;

Or let APEX handle it automatically at session end.

Multiple File Upload Example

If File Browse item allows multiple files:

FOR rec IN (
    SELECT *
    FROM   apex_application_temp_files
    WHERE  created_by = :APP_USER
)
LOOP
    -- process each file
END LOOP;

Difference: Temp Files vs Collections

Feature	Temp Files	Collections
Stores BLOB	✅	✅
Session-based	✅	✅
Structured columns	❌	✅
Good for staging data	⚠️ Limited	✅
Good for file upload	✅	✅ (after copy)

Best practice:

👉 Use APEX_APPLICATION_TEMP_FILES only as entry point
👉 Move data into collections or your own tables

Performance & Best Practices

✔ Move files to permanent table ASAP
✔ Avoid keeping huge BLOBs in collection unnecessarily
✔ Clean up collections when done
✔ Limit file size in File Browse item

Understanding file uploads in APEX completes the picture of APEX Collections.

The typical enterprise pattern is:

File Upload
    ↓
APEX_APPLICATION_TEMP_FILES
    ↓
APEX_COLLECTION (validation / staging)
    ↓
Permanent Tables

Final Thoughts

APEX Collections are one of the most powerful features in Oracle APEX that many beginners overlook.

When I first found them, I was trying to solve:

“How do I temporarily store rows without committing them?”

Collections were the answer.

They are:

Simple
Powerful
Session-aware
Highly flexible

Mastering them significantly improves your ability to build professional-grade APEX applications.

And don’t forget, you can have multiple collections in your session, just name them distinctively.