🧩 What Is an APEX Collection?
🧩 What Is an
APEX Collection?
An APEX Collection is like a temporary
in-memory table that stores data only for the current APEX session.
It’s perfect when you need to:
Capture user input temporarily.
Avoid creating a real database table.
Process, review, or update data before saving
it permanently.
Stored inside the built-in view:
sql
APEX_COLLECTIONS
🧱 The Structure of a Collection
Each collection has:
|
Type |
Columns |
Example |
|
|
Character |
50 (C001–C050) |
Names |
text data |
|
Number |
50 (N001–N050) |
IDs |
prices |
|
Date |
5 (D001–D005) |
Dates |
|
|
CLOB |
1 (CLOB001) |
Long text |
|
|
BLOB |
1 (BLOB001) |
Images/files |
|
✅ So, max 106
usable columns per collection.
✅ Limit: 1000
collections per session.
Now
For this session we will use EMPLOYEES table and its column to understand it
well and can clearly understand how to handle all data types — VARCHAR2,
NUMBER, and DATE — in a collection.
------------------------------------------------------------------------------------------------
⚙️ Step-by-Step: Create /
Insert / Update / Truncate / Fetch / Display
Let’s walk through your code and explain
each section clearly.
🏗️ Step 1 — Create a
Collection (If Not Exists)
sql
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('EMP_COLLECTION') THEN
APEX_COLLECTION.CREATE_COLLECTION('EMP_COLLECTION');
END IF;
👉 This ensures your collection
EMP_COLLECTION exists before you insert anything.
It’s like saying “if the temp table doesn’t
exist, create it.”
------------------------------------------------------------------------------------------------
➕ Step 2 — Add Data (Insert Mode)
sql
IF :P1_ACTION='A' THEN
DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT() INTO
v_count
FROM apex_collections
WHERE collection_name =
'EMP_COLLECTION'
AND c001 = :P1_EMP_ID;
IF NVL(v_count,0) = 0
THEN
APEX_COLLECTION.ADD_MEMBER(
p_collection_name
=> 'EMP_COLLECTION',
p_c001 =>
:P1_EMP_ID,
p_c002 =>
:P1_EMP_NAME,
p_c003 =>
:P1_DESIGNATION,
p_c004 =>
:P1_DEPT_ID
);
END IF;
END;
✅ Explanation:
Checks if the first name already exists (avoid
duplicates).
If
not found, adds a new member to the EMP_COLLECTION collection.
Each
row automatically gets a SEQ_ID (sequence number).
✏️ Step 3 — Update Existing Record
sql
ELSE
DECLARE
v_seq VARCHAR2(100);
BEGIN
IF
APEX_COLLECTION.COLLECTION_EXISTS('EMP_COLLECTION') THEN
SELECT seq_id INTO
v_seq
FROM
apex_collections
WHERE
collection_name = 'EMP_COLLECTION'
AND seq_id = :P1_SEQ_ID
AND c001 =
:P1_EMP_ID;
IF NVL(v_seq,'xx')
<> 'xx' THEN
APEX_COLLECTION.UPDATE_MEMBER(
p_collection_name => 'EMP_COLLECTION',
p_seq =>
v_seq,
p_c001 =>
:P1_EMP_ID,
p_c002 =>
:P1_EMP_NAME,
p_c003 =>
:P1_DESIGNATION,
p_c004 =>
:P1_DEPT_ID
);
END IF;
END IF;
END;
END IF;
✅ Explanation:
Finds the SEQ_ID (unique identifier for that
record).
Updates the matching record’s
values in the collection.
🗑️ Step 4 — Truncate (Clear
All Rows)
sql
BEGIN
APEX_COLLECTION.TRUNCATE_COLLECTION(p_collection_name =>
'EMP_COLLECTION');
END;
✅ Removes all
members (rows) from the collection but keeps the structure.
Useful for “Clear All” or “Reset Form”
actions.
🔍 Step 5 — Fetch a Single Row (For
Editing)
sql
IF :P1_ACTION='E' THEN
SELECT
C001, C002, C003, C004
INTO
:P1_EMP_ID,
:P1_EMP_NAME, :P1_DESIGNATION, :P1_DEPT_ID
FROM apex_collections
WHERE collection_name =
'EMP_COLLECTION'
AND seq_id = :P1_SEQ_ID;
END IF;
✅ When user
clicks “Edit”, this fetches the data for that row and populates the page items.
📋 Step 6 — Display Collection Data in
a Report
sql
SELECT
seq_id,
c001 AS first_name,
c002 AS last_name,
c003 AS college,
c004 AS company
FROM apex_collections
WHERE collection_name = 'EMP_COLLECTION';
✅ Use this SQL
as the source for your Classic Report or Interactive Report region.
✅ Displays all
collection members like a table.
------------------------------------------------------------------------------------------------
⚡ Tips
1. Explain the analogy:
“A
collection is a temporary data table that exists only for the session.”
2. Use Page Items meaningfully:
P1_EMP_ID, P1_EMP_NAME, etc., are inputs.
P1_ACTION
controls the mode (A=Add, E=Edit, etc.).
P1_SEQ_ID
helps to find the correct record.
3. Demonstrate live:
Show adding a record.
Show editing and updating.
Show “clear all” using truncate.
Show the report updating instantly.
4. Note: When session ends, all collection
data is lost.
You can move it later to a permanent table if needed.
✅ Quick Reference Summary
|
Task |
Procedure / Function |
Description |
|
Create Collection |
APEX_COLLECTION.CREATE_COLLECTION |
Create a new collection for storing temporary session data |
|
Add Data |
APEX_COLLECTION.ADD_MEMBER |
Insert a new row (member) into the collection |
|
Update Data |
APEX_COLLECTION.UPDATE_MEMBER |
Update an existing record in the collection using sequence ID |
|
Delete Row |
APEX_COLLECTION.DELETE_MEMBER |
Delete a specific record from the collection using sequence ID |
|
Delete All |
APEX_COLLECTION.DELETE_COLLECTION |
Completely remove the collection from the session |
|
Truncate |
APEX_COLLECTION.TRUNCATE_COLLECTION |
Remove all rows but keep the collection structure |
|
Check Exists |
APEX_COLLECTION.COLLECTION_EXISTS |
Check whether a collection already exists in the session |
|
Select Data |
SELECT * FROM APEX_COLLECTIONS |
Query collection data for reports or debugging |
Top 10 APEX Collection Interview & Workshop Questions
💡 1.
What is the difference between an APEX Collection and a regular database table?
Answer:
An APEX Collection is a temporary,
session-specific, in-memory table, while a database table is permanent and
shared across users.
Collections are
automatically cleared when a user’s session ends, making them ideal for
temporary storage such as form wizards, staging data, or interactive grids.
💡 2. Can
I store more than 50 C columns or add my own column names in an APEX
Collection?
Answer:
No — Oracle APEX defines a fixed structure:
C001–C050 (character)
N001–N050 (number)
D001–D005 (date)
CLOB001, BLOB001
You
cannot add custom column names or increase these limits, but you can repurpose
them as needed (for example, C001 = “First Name”, C002 = “Last Name”, etc.).
----------------------------------------------------------------------------------------------------------------------
💡 3. How
long does data in an APEX Collection persist?
Answer:
Collection data exists only for the
duration of the APEX session.
Once the user logs out or the session times
out, the data is automatically deleted.
If you want to keep it, you must copy it
into a real database table before the session ends.
💡 4. Can
multiple users access the same collection name?
Answer:
Yes, but each session has its own copy of
that collection.
For example, if both users create a
collection named 'EMP_COLLECTION', they each get an independent version — no
data overlap occurs between sessions.
----------------------------------------------------------------------------------------------------------------------
💡 5.
What happens if I call CREATE_COLLECTION for an already existing collection?
Answer:
Oracle will raise an error.
To safely handle that, use:
sql
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMP_COLLECTION');
This will create it if missing, or clear it
if it already exists — very useful for reinitializing collections during page
load.
----------------------------------------------------------------------------------------------------------------------
💡 6. How
can I check how many rows are stored in a collection?
Answer:
You can count rows using:
sql
SELECT COUNT()
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = 'EMP_COLLECTION';
This gives you the number of records currently in that collection for the active session.
💡 7. Can
I join a collection with a database table in SQL?
Answer:
Yes ✅ — because collections are accessible like a normal table via the
APEX_COLLECTIONS view.
Example:
sql
SELECT c.c001 AS emp_name, e.department
FROM apex_collections c
JOIN employees e ON c.c002 = e.emp_id
WHERE c.collection_name = 'EMP_COLLECTION';
This allows you to combine temporary
collection data with real tables.
----------------------------------------------------------------------------------------------------------------------
💡 8.
What happens if I use DELETE_COLLECTION instead of TRUNCATE_COLLECTION?
Answer:
TRUNCATE_COLLECTION removes all rows but keeps
the collection structure, so you can reuse it.
DELETE_COLLECTION completely removes the
collection from memory — you’d have to recreate it before inserting again.
----------------------------------------------------------------------------------------------------------------------
💡 9. How
can I export collection data to a table permanently?
Answer:
You can use an INSERT SELECT statement like
this:
sql
INSERT INTO my_table (first_name, last_name, college, company)
SELECT c001, c002, c003, c004
FROM apex_collections
WHERE collection_name = 'EMP_COLLECTION';
This transfers your temporary data into a
permanent database table.
------------------------------------------------------------------------------------------------
💡 10.
Can I view all collections currently active in my session?
Answer:
Yes. Use:
sql
SELECT DISTINCT collection_name
FROM apex_collections;
----------------------------------------------------------------------------------------------------------------------
Note [APEX COLLECTION DOCX LINK]: APEX_COLLECTION
Comments
Post a Comment