Working with APEX Collections - Expert Oracle Application Express - page 426

end if;
end;
This approach offers a cleaner way to store the data in your scenario, of course, but it also highlights
one of the overloaded UPDATE MEMBER ATTRIBUTE procedures available in the APEX COLLECTION API. More
specifically, UPDATE MEMBER ATTRIBUTE comes in six overloaded varieties—one for each of the support
APEX Collection datatypes. Listing 10-8 calls the one that's intended for the numeric columns, and
because you're updating the second numeric column, APEX COLLECTIONS.N002 , you pass in a
p attr number value of 2.
■ Note APEX versions prior to 4.x did not support numeric, date, BLOB, or XMLTYPE data. Therefore, the
equivalent overloaded update procedures are not available in those versions.
To delete an attribute member is a simple matter of calling the DELETE MEMBER procedure in
APEX COLLECTION . You can see this in action in the Sample Database Application when users click the “X”
link next to an item selected in their shopping carts. Also defined on page 12 of the application, the call
just requires the two values that effectively key your collections view, the collection name, and the
sequence ID of the member to be removed, as shown in Listing 10-9.
Listing 10-9. Removing a Member from the ORDER Collection
for x in
(select seq id, c001 from apex collections
where collection name = 'ORDER' and c001 = :P12 PRODUCT ID)
loop
apex collection.delete member(p collection name => 'ORDER', p seq => x.seq id);
end loop;
This simple DELETE MEMBER prodecure works fine for the default implementation within the Sample
Database Application, but it would not suffice for the more advanced implementation we suggested
when using UPDATE MEMBER . In that case, your collection can store multiple instances of a selected
product per row, so you need to adjust your code to update it accordingly. As shown in Listing 10-10, this
is accomplished with the same procedure call used in the more advanced implementation of the
addition of new members.
Listing 10-10. Removing a Product from the ORDER Collection
begin
-- get seq id of member containing product to be removed
for x1 in (select seq id, n002
from apex collections
where collection name = 'ORDER'
and c001 = :P12 PRODUCT ID)
loop
-- reduce quantity of collection member by 1
apex collection.update member attribute (p collection name => 'ORDER',
p seq => x1.seq id,