Oracle Apex Collection: Update Member Examples

In this tutorial, you will learn how to update collections in Oracle Apex using the APEX_COLLECTION.UPDATE_MEMBER procedure.

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 ADD_MEMBER¹, UPDATE_MEMBER², and 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 APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION.
• Then, we add two members to the 'EMPLOYEE_INFO' collection, each with a name (p_c001), an age (p_n001), and a hire date (p_d001).

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_MEMBER procedure 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';

Output:

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_ATTRIBUTE procedure 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

References:

1. Oracle ADD_MEMBER Procedure ↩︎
2. Oracle UPDATE_MEMBER Procedure ↩︎