Search for a command to run...
Oracle APEX already includes Font APEX as the default icon library for Universal Theme. In most cases, that is enough. But in one of my APEX apps, I needed a few icons that were not available there, s
Looking to give your Oracle APEX mobile app a modern, native feel? Here’s a quick hack: add a floating action button that stays anchored at the bottom corner just like in popular mobile apps. This sma
APEX 26.1 has been updated to the Universal Theme. While much of the attention in this release is on AI features, I think the UT improvements are just as important for developers like me who care abou
The apex_barcode package offers a PL/SQL API to generate various types of barcodes. You can output barcodes as SVG (Scalable Vector Graphics) values or as PNG file blobs, making it easy to embed them
Oracle APEX makes it easy to get data on screen fast, but often, the default tabular output isn’t enough. What if you want to highlight statuses, add icons, or display badges all without writing compl
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.
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.
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.
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.