State Management for Multi-Step Forms in Oracle APEX Using Collections

When building enterprise-level applications in Oracle APEX, multi-step forms—also known as wizards—are often necessary for onboarding processes, surveys, dynamic form generation, or multi-part configurations. However, managing form data across these steps without prematurely saving it to the database can be challenging.

In this blog, we’ll explore how to use APEX Collections as a robust and flexible solution for managing temporary states across steps. This approach keeps your actual tables clean until the final submission and allows full user flexibility to move forward and backward without data loss.

Why Use APEX Collections for Multi-Step Forms?

Oracle APEX Collections are session-specific, in-memory data structures that allow temporary storage of rows and columns. They are ideal for cases where:

You don’t want to insert/update the database yet.
You need to allow back-and-forth navigation between steps.
You need to validate or review all data before final submission.
You want to avoid the complexity of passing data via page items or hidden fields.

Architecture

Here’s a simplified flow:

[Step 1] → [Step 2] → [Step 3] → [Review] → [Final Submit]

At each step:

User input is stored in an APEX Collection.
When the user clicks “Next”, the app writes values into the collection.
On “Back”, values are retrieved from the collection and populated into items.
On “Final Submit”, the collection values are inserted into your actual database table(s).

Understanding Data Types and Limits in APEX Collections:

Oracle APEX Collections support a wide variety of data types, making them extremely flexible for holding all kinds of temporary state data.

Column Name	Data Type	Description
`C001` to `C050`	`VARCHAR2(4000)`	Up to 50 text columns, commonly used for field values like names, emails, etc.
`N001` to `N005`	`NUMBER`	Up to 5 numeric columns, used for amounts, step indicators, etc.
`D001`	`DATE`	Single date column for timestamps or deadlines
`CLob001`	`CLOB`	For large text data like JSON, XML, descriptions
`Blob001`	`BLOB`	For storing files temporarily before final upload

You can think of a collection as a flexible temporary table. Here’s how they are often mapped:

C001 → Customer Name
C002 → Email Address
C003 → Phone Number
N001 → Current Step
CLob001 → JSON or survey answers
Blob001 → Uploaded file (optional)

Important: APEX collections are stored in session memory, so storing large CLOBs or BLOBs should be handled carefully to avoid memory pressure or timeouts.

Step-by-Step Implementation

Step 1: Initialize the Collection

Create a Before Header process on the first page (Step 1):

Copy Code

BEGIN
   IF NOT APEX_COLLECTION.COLLECTION_EXISTS('MULTISTEP_FORM') THEN
      APEX_COLLECTION.CREATE_COLLECTION('MULTISTEP_FORM');
   END IF;
END;

You can also clear any previous data to start fresh

Copy Code

APEX_COLLECTION.DELETE_COLLECTION('MULTISTEP_FORM');

Step 2: Add Data to Collection at Each Step

Use a Before Navigation (or “Next” button) process to add or update data:

Copy Code

DECLARE
   l_seq_id NUMBER;
BEGIN
   APEX_COLLECTION.ADD_MEMBER (
      p_collection_name => 'MULTISTEP_FORM',
      p_c001            => :P1_NAME,
      p_c002            => :P1_EMAIL,
      p_c003            => :P1_PHONE,
      p_clob001         => :P1_JSON_DATA,
      p_d001            => SYSDATE
   );
END;

To update a row (e.g., if they go back and edit):

Copy Code

APEX_COLLECTION.UPDATE_MEMBER (
   p_collection_name => 'MULTISTEP_FORM',
   p_seq             => l_seq_id,
   p_c001            => :P1_NAME,
   p_c002            => :P1_EMAIL,
   p_c003            => :P1_PHONE,
   p_clob001         => :P1_JSON_DATA
);

Step 3: Retrieve Data on Back Navigation

On page load of a step (like Page 1), if user is coming back:

Copy Code

DECLARE
   l_seq_id NUMBER;
BEGIN
   SELECT SEQ_ID INTO l_seq_id
   FROM APEX_COLLECTIONS
   WHERE COLLECTION_NAME = 'MULTISTEP_FORM'
   AND ROWNUM = 1;

   :P1_NAME  := APEX_COLLECTION.GET_MEMBER_ATTRIBUTE('MULTISTEP_FORM', l_seq_id, 1);
   :P1_EMAIL := APEX_COLLECTION.GET_MEMBER_ATTRIBUTE('MULTISTEP_FORM', l_seq_id, 2);
   :P1_PHONE := APEX_COLLECTION.GET_MEMBER_ATTRIBUTE('MULTISTEP_FORM', l_seq_id, 3);
END;

This ensures the user sees their previously entered data when navigating backward.

Step 4: Final Submission — Insert into Database

On the Review or Final Submit page, insert data into your base table:

Copy Code

FOR rec IN (
   SELECT * FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'MULTISTEP_FORM'
)
LOOP
   INSERT INTO USERS (
      name, email, phone, notes
   ) VALUES (
      rec.c001, rec.c002, rec.c003, rec.clob001
   );
END LOOP;

APEX_COLLECTION.DELETE_COLLECTION('MULTISTEP_FORM');

Validation & Error Handling

Always validate data before insert/update.

Copy Code

IF :P1_NAME IS NULL THEN 
apex_error.add_error(
p_message => 'Name is required.', 
p_display_location => apex_error.c_inline_in_notification 
); 
END IF;

Prevent duplicate entries by checking existing keys in C001.

Use Cases for APEX Collections

Oracle APEX Collections offer a powerful, session-persistent, in-memory data structure that can be used creatively beyond simple wizard data capture. Below are real-world use cases where collections truly shine, with expanded descriptions to inspire their use in production systems.

1. Multi-Step Form (Wizard) State Management

In multi-page wizards, users enter data across different screens. APEX Collections serve as a temporary staging area, storing user input at each step. This allows the application to preserve input even when navigating back and forth without committing data to the database until final confirmation.

2. Temporary Data Holding for Bulk Operations

Collections are ideal for scenarios like uploading data via Excel/CSV. Store the parsed data in a collection, show it in a report for user review and edits, and only then commit it to the table. This ensures data integrity and gives control over what gets saved.

3. Dynamic JSON/XML Construction

When building dynamic payloads for REST APIs, use collections to gather user inputs, enrich them with lookup values, and construct final JSON or XML from collection rows. The structure is clean, and using CLOB001 makes it easy to hold the final payload temporarily.

4. Staging External Data from Web Services

Instead of inserting data fetched via REST services directly into base tables, use collections to temporarily hold and display them. This gives users a chance to verify, transform, or map the incoming data before saving.

5. Change Tracking and Audit Preparation

You can load existing records into a collection, let users make changes in an editable report, and compare new values with old ones in PL/SQL. This enables pre-save change tracking or even audit trail creation without altering original tables until necessary.

6. Session-Based Shopping Carts / Order Builders

For e-commerce-style pages, use collections to hold items added to a cart or order. Once the user finalizes the list, store the entire order in base tables. This works great for both authenticated and public sessions.

7. Cross-Page Filters and Temporary Search Context

If users filter data and navigate to other pages (e.g., for drill-down), store the filter context in a collection. When they return, you can restore the original filter without relying on session state or hidden items.

8. Temporary Aggregations or Business Logic Calculations

Perform business logic on-the-fly by inserting data into collections and then using SQL/PLSQL to calculate totals, averages, or flags before inserting into the final table. This is useful in budgeting, planning, or any scenario involving intermediate values.

9. Version Comparison (Before vs After Edits)

Use two collections — one for the original dataset, and another for the user-edited version. You can then run row-by-row comparisons to determine which fields changed, helping in version control or change approval systems.

10. Offline Data Capture Support

In limited offline setups (PWA-style), user inputs can be saved in local storage and synced into collections when online resumes. This pattern allows smooth delayed-sync flows using APEX Collections as intermediaries.

Use a Key Column

In scenarios where collections store multiple rows — such as multi-row forms or data grouped by users — it’s best to assign a logical key to each row. Use a column like C001 to hold a ROW_ID, CUSTOM_ID, or any unique reference. This enables precise control when updating or deleting specific rows within the collection, especially during navigation between steps or when users revisit the form.

Example: Store CUSTOM_ID in C001, and update other columns like C002 (Name), C003 (Email), etc., using this key in your logic.

Consistent Naming

Consistency in naming page items simplifies logic. If you’re capturing a field like “Name” across multiple pages, follow a pattern like :P1_NAME, :P2_NAME, etc. This makes your PL/SQL processes easier to scale and reduces the chances of referencing the wrong item when handling validations or data movement.

Handle Session Timeout Gracefully

Remember: APEX Collections are session-based. If the session times out — due to inactivity or connectivity loss — the collection and its data are lost. You can mitigate the impact by:

Adding session timeout warnings
Using browser storage/local cache (advanced)
Displaying session timeout warnings using client-side JavaScript to alert users before expiration.
Optionally using local browser storage (sessionStorage/localStorage) to back up form inputs temporarily. This is an advanced strategy suitable for longer forms or progressive data entry.
Providing a “Save Progress” feature to persist critical data into database tables at defined checkpoints (e.g., after completing Step 2)

Common Use Cases

Customer onboarding wizards
Complex form configurations (multi-tabbed input)
Survey forms where answers must be reviewed before final submission
File uploads across steps (can link to your file upload blog here)

Conclusion

Multi-step forms in Oracle APEX often demand better state management than what page items or hidden fields can offer. APEX Collections provide a clean, scalable way to store and manage user input temporarily, ensuring both flexibility and data integrity.

By leveraging collections, you can build robust wizard-style flows that are user-friendly, reversible, and secure — all without committing to the database until the user is truly ready.