How Do I Use PL/SQL with APEX Collections

 Introduction

Oracle APEX Collections provide a flexible way to store and manage session-based data in memory, which is especially useful when dealing with temporary datasets like shopping carts, multi-step wizards, or user-specific records. By using PL/SQL with APEX Collections, developers can insert, update, delete, and query data stored in collections throughout the user's session. This allows for powerful data manipulation without committing anything to the database until needed.

How Do I Use PL/SQL with APEX Collections
Font: Arial, font size: 14px, plain text

In Oracle APEX, APEX Collections are in-memory structures used to store and manage temporary data specific to a user’s session. Using PL/SQL with collections allows you to manipulate session-specific data without committing it to a permanent table, which is useful in use cases such as shopping carts, wizard-style forms, or temporary staging areas.

Step 1: Create or Add Data to a Collection
To begin using a collection, you first need to create it. Collections are created automatically when you add data to them using the APEX_COLLECTION.ADD_MEMBER procedure.

BEGIN
  APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => 'MY_CART',
    p_c001            => :P1_PRODUCT_NAME,
    p_n001            => :P1_PRICE);
END;

In this example, a collection named MY_CART is created (if it doesn't already exist), and the values from page items P1_PRODUCT_NAME and P1_PRICE are stored in the character and number columns of the collection.

Step 2: Check If the Collection Exists
You can check if the collection already exists before trying to use it.

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

Step 3: Query Data from a Collection
You can query the data from a collection using SQL, usually in a report region.

SELECT seq_id,
       c001 AS product_name,
       n001 AS price
FROM apex_collections
WHERE collection_name = 'MY_CART'

Step 4: Update a Collection Member
To update a member of a collection, use APEX_COLLECTION.UPDATE_MEMBER.

BEGIN
  APEX_COLLECTION.UPDATE_MEMBER(
    p_collection_name => 'MY_CART',
    p_seq             => :P1_SEQ_ID,
    p_c001            => :P1_PRODUCT_NAME,
    p_n001            => :P1_NEW_PRICE);
END;

Step 5: Delete a Member from a Collection
Use APEX_COLLECTION.DELETE_MEMBER to remove one row by its sequence ID.

BEGIN
  APEX_COLLECTION.DELETE_MEMBER(
    p_collection_name => 'MY_CART',
    p_seq             => :P1_SEQ_ID);
END;

Step 6: Delete the Entire Collection
Use DELETE_COLLECTION to remove the entire collection from session memory.

BEGIN
  APEX_COLLECTION.DELETE_COLLECTION('MY_CART');
END;

Step 7: Use Collections Across Pages
Since collections are session-based, their data is preserved while the user navigates through pages. This is helpful in multi-step wizards or applications where you collect and hold temporary data until a final submission.

Best Practices

• Always check if the collection exists before creating or modifying it.

• Use column aliases (C001–C050 for VARCHAR2, N001–N005 for NUMBER, D001–D005 for DATE) wisely to store relevant data.

• Clear collections when they are no longer needed to save session memory.

• Keep in mind that collections are specific to the session and will disappear when the session ends.

Using PL/SQL with APEX Collections gives you full control over temporary data handling, making your application more dynamic and responsive without constantly writing to the database.

Conclusion
Using PL/SQL with APEX Collections enhances your application's ability to manage temporary and user-specific data in a secure and scalable way. Collections allow you to work with structured data just like regular tables, all within a session's scope. Mastering PL/SQL operations with collections opens up dynamic use cases and improves user experience by maintaining data context across pages without requiring permanent storage.