8.5.7.1 Key Collections Concepts

A collection requires no formal definition. Instead, you use its predefined generic columns, such as N001, N002, C001, C002, and D001, according to the number and type of values you need to store.

Collections support a fixed number of typed columns. For a given collection, just use the columns you need in each row.

Table 8-1 Collection Columns

Data Type	Column Name(s)	Quantity
VARCHAR2	c001 to c050	50
NUMBER	n001 to n005	5
DATE	d001 to d005	5
CLOB	clob001	1
BLOB	blob001	1
XMLTYPE	xmltype001	1

You add rows – also known as members – to a collection using the ADD_MEMBER procedure or function. Each member is assigned a sequence id (SEQ_ID) that is one larger than the current maximum sequence id in the collection. Using that sequence id, you can delete a member or update a member using DELETE_MEMBER or UPDATE_MEMBER procedures, respectively.

To access the data in a collection, you query the APEX_COLLECTIONS view using a WHERE clause of:

WHERE collection_name = 'COLLECTION_NAME'

For example, imagine creating a collection named CART to represent a shopping cart. You need to store:

• Item code (VARCHAR2)
• Quantity (NUMBER)
• Need by date (DATE)

To create the collection, use the statement:

apex_collection.create_collection(
   p_collection_name => 'SHOPPING_CART');

You can use any column of appropriate data type, but by convention you use them in numerical order. So, you'll use column c001 to store the Item code, n001 to store the quantity, and d001 to store the date. Therefore, to add two items to the cart, use code like this:

apex_collection.add_member(
   p_c001 => 'COMP-APPL-MBP-16',
   p_n001 => 2,
   p_d001 => date'2025-05-27');
apex_collection.add_member(
   p_c001 => 'ACC-APPL-MAGICMOUSE',
   p_n001 => 1,
   p_d001 => date'2025-05-27');

To retrieve the members in the collection, write a query like the following. It is helpful to alias the columns to more meaningful names as follows:

select c001 as item_code,
       n001 as quantity,
       d001 as need_by_date
  from apex_collections
 where collection_name = 'SHOPPING_CART'

APEX automatically isolates the session state of one user session from another, so you can write the query knowing that you will only ever see the data the current user session has added to the collection named SHOPPING_CART. You can use a query like this in any APEX region to present collection data in any way you might need to.

You can also use a query over APEX_COLLECTIONS in a PL/SQL cursor for loop to iterate over the contents of the collection like this:

for i in (select c001 as item_code,
                 n001 as quantity,
                 d001 as need_by_date
            from apex_collections
           where collection_name = 'SHOPPING_CART')
loop
   -- Reference i.item_code, i.quantity, 
   -- and i.need_by_date in the loop
end loop;

To proactively clear a collection before the session ends, use the TRUNCATE_COLLECTION procedure. Consult the APEX PL/SQL reference documentation for the APEX_COLLECTION package for other useful functions and procedures for working with collections.

Tip:

If you combine an editable grid with a collection, you will need to make two modifications to the typical no-code setup. First, since the grid depends on a primary key value that does not change you will need to populate one of the collection columns with a unique key. Consider generating a SYS_GUID value as the primary key for each row as the package body in the following section does.