# APEX Collections to manage temporary data

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.

```sql
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](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_COLLECTION.html).

```sql
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.

```sql
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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1685488054130/8dda5b76-675a-42c2-9fa0-a5365dc6a05b.png align="center")

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.

```sql
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.

```sql
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

```sql
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.