The APEX_COLLECTION (Oracle 12c onwards) package can be a good choice when you need to work with
data in a temporary, in-memory table-like structure.
When should I use the APEX_COLLECTION?
- Temporary data storage: If you need to temporarily store data that is
generated by a report or a form, using APEX_COLLECTION can be a good option. Since the
data is stored in memory rather than in a physical table in the database, you don’t need
to worry about the overhead of creating and managing a database table. - Working with large datasets: If you need to work with large datasets,
APEX_COLLECTION can be more efficient than other options, such as creating a temporary
table in the database. Since the data is stored in memory, it can be accessed and
manipulated more quickly than data that is stored in a physical table. - Non-persistent data: If you don’t need to persist the data between
sessions or across different users, APEX_COLLECTION can be a good choice. Since the data
is stored in memory, it is automatically cleared when the session ends or when the user
logs out. - Flexible data structure: APEX_COLLECTION provides a flexible data
structure that can be easily modified at runtime. You can add, update, and delete
records as needed, and you can define the structure of the collection dynamically based
on your requirements.
When should I avoid it?
While APEX_COLLECTION can be a useful tool for working with temporary data in Oracle APEX,
there are some scenarios where it may not be the best choice. Here are a few situations
where you may want to consider alternative approaches:
- Large or complex datasets: While APEX_COLLECTION can be efficient for
working with large datasets, it may not be the best choice for very large or complex
datasets. In such cases, it may be better to use a physical table in the database or a
dedicated data store, such as a NoSQL database or a document store. - High transaction volume: If you have a high volume of transactions or
concurrent users, APEX_COLLECTION may not be able to keep up with the demand. In such
cases, it may be better to use a dedicated database table or another data storage
mechanism that is optimized for high performance. - Data persistence: If you need to persist the data across sessions or
across different users, APEX_COLLECTION may not be the best choice. While the data is
stored in memory and can be accessed quickly, it is not durable and will be lost when
the session ends or when the user logs out. In such cases, it may be better to use a
dedicated database table or another data storage mechanism that is designed for
durability and persistence. - Complex data structures: While APEX_COLLECTION can be flexible and
dynamic, it may not be the best choice for working with very complex data structures or
data types. If you need to work with data that has a complex or hierarchical structure,
it may be better to use a dedicated data store or a specialized data modeling tool that
is designed to handle such data.
That being said, there are also some limitations to using APEX_COLLECTION, such as the
inability to create indexes or enforce constraints, and the limited support for complex data
types. In some cases, it may be more appropriate to use a physical table or other data
storage
mechanism, depending on your specific requirements and use case.
with it.
Using APEX_COLLECTION
Create an APEX collection:
You can create an APEX collection using the APEX_COLLECTION.CREATE_COLLECTION procedure. This
procedure
creates a new collection with the specified name and collection type.
BEGIN
APEX_COLLECTION.CREATE_COLLECTION(
p_collection_name => 'MY_COLLECTION',
p_collection_type => 'APEX_COLLECTION_TYPE_VARCHAR2'
);
END;
Add records to the collection:
You can add records to an APEX collection using the APEX_COLLECTION.ADD_MEMBER procedure. This
procedure
adds a new record to the specified collection with the specified values.
BEGIN
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_c001 => 'John',
p_c002 => 'Doe',
p_c003 => 'johndoe@example.com'
);
END;
Access records in the collection:
You can access the records in an APEX collection using the APEX_COLLECTION.GET_MEMBERS function. This
function returns a cursor that can be used to iterate over the records in the collection.
DECLARE
l_cursor apex_t_varchar2;
l_rec apex_collection_types.apex_collection_varchar2_nt;
BEGIN
l_cursor := apex_collection.get_members(p_collection_name => 'MY_COLLECTION');
LOOP
FETCH l_cursor BULK COLLECT INTO l_rec LIMIT 100;
FOR i IN 1..l_rec.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_rec(i).c001 || ' ' || l_rec(i).c002 || ': ' || l_rec(i).c003);
END LOOP;
EXIT WHEN l_cursor%NOTFOUND;
END LOOP;
END;
Merge records in the collection:
You can merge records in an APEX collection using the APEX_COLLECTION.MERGE_COLLECTION procedure. This
procedure merges the specified records from the source collection into the target collection.
BEGIN
APEX_COLLECTION.MERGE_COLLECTION(
p_target_collection_name => 'MY_COLLECTION',
p_source_collection_name => 'OTHER_COLLECTION',
p_member_key => '1',
p_update_column => 'C003',
p_update_value => 'newvalue@example.com'
);
END;
Change records in the collection:
You can change records in an APEX collection using the APEX_COLLECTION.UPDATE_MEMBER procedure. This
procedure
updates the specified record in the collection with the specified values.
BEGIN
APEX_COLLECTION.UPDATE_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_seq => 1,
p_c001 => 'Jane',
p_c002 => 'Doe',
p_c003 => 'janedoe@example.com'
);
END;
Delete records from the collection:
You can delete records from an APEX collection using the APEX_COLLECTION.DELETE_MEMBER procedure. This
procedure
deletes the specified record from the collection.
BEGIN
APEX_COLLECTION.DELETE_MEMBER(
p_collection_name => 'MY_COLLECTION',
p_seq => 1
);
END;
Conclusion
That’s it! Using the APEX_COLLECTION package, you can create, access, merge, change, and delete records in a
collection in Oracle APEX.
