The APEX collection is a temporary structure, similar to a temporary table. Data stored in it can be accessed throughout the application and is retained until the session ends or is intentionally deleted. A prime example of its usage is the shopping cart feature in an online store. As a user browses the store, potential purchases are added to the cart and temporarily stored. If the user abandons the purchase and closes the browser, the collection containing the items is deleted, preventing unnecessary data storage in the database.

COLLECTION_NAME   NOT NULL VARCHAR2(255)
SEQ_ID            NOT NULL NUMBER 
C001              VARCHAR2(4000)
C002              VARCHAR2(4000)
C003              VARCHAR2(4000)   
C004              VARCHAR2(4000)   
C005              VARCHAR2(4000)  
...
C050              VARCHAR2(4000)
N001              NUMBER
N002              NUMBER
N003              NUMBER
N004              NUMBER
N005              NUMBER     
D001              DATE
D002              DATE
D003              DATE
D004              DATE
D005              DATE
CLOB001           CLOB
BLOB001           BLOB  
XMLTYPE001        XMLTYPE
MD5_ORIGINAL      VARCHAR2(4000)

It is not possible to insert data into a collection using DML commands; instead, an API called APEX_COLLECTION is used.

Create a Collection

Before using a collection, we must initialize or create it. To do this, we need a name that cannot exceed 255 characters, regardless of whether it contains uppercase or lowercase letters. When the collection is created, this name is automatically converted to uppercase. It is possible to create a collection and then insert the data, or to create the collection by providing an SQL query that will generate the collection with the data returned by the query. In the example below, I will simply create the collection and then insert the data.

See all the options here.

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

Now that the collection has been created, we can proceed to insert the data.

BEGIN
  apex_collection.add_member(p_collection_name => 'SHOPPING_CART',
                             P_C001 => 'SHIRT001', -- product code
                             P_C002 => 'M', -- Size
                             P_C003 => 'blue001', -- color code
                             P_N001 => 1); -- Amount
END;

If we access the “Session” option from the APEX developer bar and select View = Collections, we can see all the collections that have data in the current session.

Note: If a collection is created and has no data, it will not appear here.

It is important to remember that in order to access the data of a collection, you must be in the same session as the application where the data was inserted. To accomplish this, simply execute a query in the APEX_COLLECTIONS view, providing the name as a parameter.

SELECT c001, c002, c003, n001
FROM APEX_collections
WHERE collection_name = 'SHOPPING_CART';

If we execute this query in an SQL Developer session or even in APEX SQL Commands, it will return no data found. As previously mentioned, we need to be in the same application session.

Change a collection record

To modify data, we can use the APEX_COLLECTION.UPDATE_MEMBER procedure; however, this procedure changes all columns. If we only wish to change the number of items from 1 to 2, we would need to resubmit all the product data while adjusting the quantity. To update only the quantity without having to provide all the data for that product again, we can use the APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE API.

BEGIN
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
          p_collection_name => 'SHOPPING_CART',
          p_seq => 1, -- Sequence or Line ID
          p_attr_number => 1, -- N001
          p_number_value => 2); -- Quantity modified to 2
END;

Note that for each type of data, we change the parameter referring to the attribute that needs modification. In this case, we use p_number_value to indicate that the data we want to change is the NUMBER in position 1, which is n001. To modify, for instance, C001, simply change p_number_value to p_attr_value.

Delete a record

To delete a record/member of the collection we can use APEX_COLLECTION.DELETE_MEMBER

BEGIN
      APEX_COLLECTION.DELETE_MEMBER(
          p_collection_name => 'SHOPPING_CART',
          p_seq => '1');
END;

We still have the option of changing only the value of a column. To do this, we use the same concept as UPDATE_MEMBER_ATTRIBUTE, but call APEX_COLLECTION.DELETE_MEMBER_ATTRIBUTE instead.

If we need to delete all data from the collection without deleting the collection itself, we can use APEX_COLLECTION.TRUNCATE_COLLECTION, passing the name of the collection as a parameter. If we want to delete both the data and the collection, we simply use APEX_COLLECTION.DELETE_COLLECTION.

It is possible to check if a collection exists by using APEX_COLLECTION.COLLECTION_EXISTS and passing the name of the collection as a parameter. This is very useful if executed before creating a collection. If a collection already exists and we try to create it again, an error will be returned. If we want to know how many records there are, just run APEX_COLLECTION.COLLECTION_MEMBER_COUNT again, passing the collection name as a parameter.

All examples featured in this blog are compatible with the APEX_COLLECTION API starting from version 4.2. This means that regardless of the version you are using, they will all function properly.