In this tutorial, you will learn how to update collections in Oracle Apex using the
In Oracle Application Express (Oracle Apex), the capability to manage collection members is fundamental for dynamic data handling. Here, we'll explore the
APEX_COLLECTION package, focusing on the
UPDATE_MEMBER_ATTRIBUTE procedures, which allow you to add, comprehensively update, and modify specific attributes of members within a named collection.
So before updating a collection, we will start by adding some data to it so that later we can update it.
Oracle Apex Collection: ADD_MEMBER example
Let's initiate the process by adding members to a collection. To accomplish this, we'll utilize the
APEX_COLLECTION.ADD_MEMBER procedure. Suppose we have a collection named 'EMPLOYEE_INFO' with character attributes for names, numeric attributes for ages, and date attributes for hire dates. Here's how we can add a few members:
BEGIN APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMPLOYEE_INFO'); APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'Employee_Info', p_c001 => 'John Doe', p_n001 => 30, p_d001 => TO_DATE('2022-01-15', 'YYYY-MM-DD')); APEX_COLLECTION.ADD_MEMBER ( p_collection_name => 'EMPLOYEE_INFO', p_c001 => 'Jane Smith', p_n001 => 25, p_d001 => TO_DATE('2021-11-10', 'YYYY-MM-DD')); END;
In the provided example:
- We first ensure that the 'EMPLOYEE_INFO' collection is created or truncated using
- Then, we add two members to the 'EMPLOYEE_INFO' collection, each with a name (
p_c001), an age (
p_n001), and a hire date (
Oracle Apex Collection: UPDATE_MEMBER example
When updating collection members, it's essential to understand that any attribute not specified in the update will be nullified. Therefore, it's necessary to provide all the columns' values for comprehensive updates. Let's say we want to update John Doe's age and hire date and Jane Smith's name. Here's how we can achieve this:
BEGIN APEX_COLLECTION.UPDATE_MEMBER ( p_collection_name => 'EMPLOYEE_INFO', p_seq => 1, p_c001 => 'John Doe', -- Retaining the name p_n001 => 32, -- Updating the age p_d001 => TO_DATE('2022-02-20', 'YYYY-MM-DD')); -- Updating the hire date APEX_COLLECTION.UPDATE_MEMBER ( p_collection_name => 'EMPLOYEE_INFO', p_seq => 2, p_c001 => 'Jane Johnson'); -- Updating the name END;
In the code above:
- We use the
APEX_COLLECTION.UPDATE_MEMBERprocedure to perform comprehensive updates on collection members.
- For John Doe's update, we specify the collection name, the sequence ID (
p_seq) of the member to update (1 in this case), and provide values for all columns (name, age, and hire date).
- For Jane Smith's update, we specify the collection name, and the sequence ID (2), and update only the name column. But in this case, other columns will be nullified.
Below is the SQL query to view the 'EMPLOYEE_INFO' collection data:
Select seq_id, c001, n001, d001 From apex_collections Where collection_name = 'EMPLOYEE_INFO';
Oracle Apex Collection: UPDATE_MEMBER_ATTRIBUTE Example
In addition to updating entire members, you can also modify specific attributes of collection members using the
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE procedure. The benefit of this procedure is that you do not need to specify values for other columns and they will not be nullified as they were using
APEX_COLLECTION.UPDATE_MEMBER PROCEDURE. Let's say we want to change John Doe's name. Here's how we can achieve this:
BEGIN APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name => 'EMPLOYEE_INFO', p_seq => 1, p_attr_number => 1, -- Attribute number for name p_attr_value => 'Vinish'); -- Updated name END;
In the code above:
- We use the
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTEprocedure to modify a specific attribute of a collection member.
- We specify the collection name, the sequence ID (
p_seq) of the member to update (1 in this case), the attribute number (1 for name), and the new attribute value.
By following these examples, you can effectively add, comprehensively update and modify specific attributes of members within a collection in Oracle Apex, ensuring that your data remains dynamic and accurately reflects your application's requirements.
Questions Asked for Oracle Apex Collections
- Collection to pass from one page to another
- Add quantity and item in the collection on click of the Card Layout Report