In Oracle Application Express (Oracle APEX), a Collection is a temporary, session-based data storage structure that allows you to store and manipulate data in memory without creating a physical table in the database.

Think of it as a temporary table for a specific user session.

What is an APEX Collection?

An APEX Collection is:

* A temporary data container

* Stored in session memory

* Accessible only during the user's session

* Automatically deleted when the session ends

It is mainly used to store intermediate data before inserting into real database tables.

Why Do We Use Collections?

Collections are useful when:

- You don’t want to create a physical table

- You want to store data temporarily

- You are building a multi-step form (wizard)

- You are working with Interactive Grid / Tabular Form

- You need staging before final commit

- You want to hold data from API response

Real-Time Example

Suppose:

You are creating a Purchase Order page.

User:

* Adds multiple items

* Reviews items

* Then clicks "Submit"

Instead of inserting each row immediately into the database, you:

1. Store rows in an APEX Collection.

2. Allow editing/deleting.

3. Finally insert into real table when confirmed.

👉 This avoids partial inserts and keeps transactions clean.

How Collection Works Internally

APEX Collections store data in the internal table:

APEX_COLLECTIONS

Each collection has:

* A collection name

* Multiple rows

* Multiple columns (50 character columns + number/date columns)

Structure of APEX Collection

Each row in collection contains:

| ----------- | ------------ |
| Character   | C001 to C050 |
| Number      | N001 to N005 |
| Date        | D001 to D005 |
| Clob        | CLOB001      |
| Blob        | BLOB001      |

So you can store:

* Text
* Numbers
* Dates
* Large text
* Files

---

#  How to Create Collection

You use the package:

sql
APEX_COLLECTION.CREATE_COLLECTION

Example:

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

# Add Data to Collection

BEGIN
   APEX_COLLECTION.ADD_MEMBER(
       p_collection_name => 'EMP_COLLECTION',
       p_c001 => '101',
       p_c002 => 'Mobashshir',
       p_n001 => 50000
   );
END;

Here:

* C001 = Employee ID

* C002 = Employee Name

* N001 = Salary

Fetch Data from Collection

SELECT c001 emp_id,
       c002 emp_name,
       n001 salary
FROM APEX_COLLECTIONS
WHERE collection_name = 'EMP_COLLECTION';

Delete Collection

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

Important Features

✔ Session specific ✔ Automatically cleared after logout ✔ No need to create table ✔ Works like temporary table ✔ Supports bulk operations ✔ Very fast for temporary storage

Common Use Cases in Real Projects

1️⃣ Shopping Cart

Store cart items before final checkout.

2️⃣ Wizard Forms

Multi-page employee registration.

3️⃣ Excel Upload

Upload file → store rows in collection → validate → insert.

4️⃣ API Integration

Store API response temporarily.

5️⃣ Interactive Grid Custom Processing

Instead of automatic row processing, use collection.

| Feature               | Collection | GTT      |
| --------------------- | ---------- | -------- |
| Session Based         | Yes        | Yes      |
| Physical Table Needed | No         | Yes      |
| Requires DBA          | No         | Yes      |
| Easy in APEX          | Very Easy  | Moderate |

---

#  Important Notes

⚠ Collection name must be in uppercase
⚠ Always check if collection exists before creating
⚠ Clear collection before reusing

Example:

sql
IF APEX_COLLECTION.COLLECTION_EXISTS('EMP_COLLECTION') THEN
   APEX_COLLECTION.DELETE_COLLECTION('EMP_COLLECTION');
END IF;

=> In Simple Words

Collection in APEX is a temporary in-memory table used to store data during a user session before saving it permanently in database.