8.5.7.2 Tips for Maintainable Collection Use

For additional ease of maintenance, create a view for the collection query so your pages can work with the meaningful column names anywhere they need to.

For example, you can create the following view. Notice that in addition to item_code, quantity, and need_by_date, it is also storing a unique id value that collection use with an interactive grid requires:

create view shopping_cart_v as
select c001 as item_code,
       c002 as id,
       n001 as quantity,
       d001 as need_by_date
  from apex_collections
 where collection_name = 'SHOPPING_CART'

You can use this view in a PL/SQL cursor for loop to iterate over the contents of the collection like this:

for i in (select item_code,
                 id,
                 quantity,
                 need_by_date
            from shopping_cart_v)
loop
   -- Reference i.item_code, i.id, 
   -- i.quantity, and i.need_by_date in the loop
end loop;

For ultimate maintainability, consider writing a PL/SQL package API that isolates the interaction with the APEX_COLLECTION package behind a set of functions and procedures that describe the business functionality it provides. For example, consider the package:

create or replace package shopping_cart_api is
    ----------------------------------------------------------
    procedure clear_cart;
    ----------------------------------------------------------
    function add_item(
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    return varchar2;
    ----------------------------------------------------------
    procedure remove_item (
        p_item_id in varchar2);
    ----------------------------------------------------------
    procedure update_item (
        p_item_id      in varchar2,
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date);
end shopping_cart_api;

Using a package specification like this, your application code is easier to understand for yourself and any teammates who can immediately infer that it's working with a shopping cart.

The corresponding package body for the SHOPPING_CART_API package appears below. Notice how it's generating a unique SYS_GUID value to provide a primary key that does not change for each row in the collection:

create or replace package body shopping_cart_api is
    c_collection_name constant varchar2(40) := 'SHOPPING_CART';
    ----------------------------------------------------------
    procedure ensure_collection
    is
    begin
        if not apex_collection.collection_exists(c_collection_name) then
            apex_collection.create_collection(c_collection_name);
        end if;
    end ensure_collection;
    ----------------------------------------------------------    
    procedure clear_cart
    is
    begin
        ensure_collection;
        apex_collection.truncate_collection(c_collection_name);
    end clear_cart;
    ----------------------------------------------------------
    function get_seq_id(
        p_item_id in varchar2)
    return number
    is
        l_ret number;
    begin
        for j in (select seq_id as seq
                    from apex_collections
                   where collection_name = c_collection_name
                     and c002 = p_item_id)
        loop
            l_ret := j.seq;
        end loop;
        return l_ret;
    end;
    ----------------------------------------------------------
    function add_item (
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    return varchar2
    is
        l_ret varchar2(255) := sys_guid();
    begin
        ensure_collection;
        apex_collection.add_member (
            p_collection_name    => c_collection_name,
            p_c001               => p_item_code,
            p_c002               => l_ret,
            p_n001               => p_quantity,
            p_d001               => p_need_by_date
        );
        return l_ret;
    end add_item;
    ----------------------------------------------------------
    procedure remove_item (
        p_item_id in varchar2) 
    is
        l_seq_id number;
    begin
        ensure_collection;
        l_seq_id := get_seq_id(p_item_id);
        if l_seq_id is not null then
            apex_collection.delete_member (
                p_collection_name => c_collection_name,
                p_seq             => l_seq_id);
        end if;
    end remove_item;
    ----------------------------------------------------------
    procedure update_item (
        p_item_id      in varchar2,
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    is
        l_seq_id number;
    begin
        ensure_collection;
        l_seq_id := get_seq_id(p_item_id);
        if l_seq_id is not null then
            apex_collection.update_member(
                p_collection_name => c_collection_name,
                p_seq             => l_seq_id,
                p_c001            => p_item_code,
                p_c002            => p_item_id,
                p_n001            => p_quantity,
                p_d001            => p_need_by_date);
        end if;
    end update_item;
end shopping_cart_api;