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.
Did you find this article valuable?
Support Rodrigo Mesquita by becoming a sponsor. Any amount is appreciated!