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

Listing 10-5. Initializing a Collection Called “Order” Using More Granular Methods

begin
if not apex collection.collection exists('ORDER') then
apex collection.create collection('ORDER');
else
apex collection.truncate collection('ORDER');
end if;
end;

The important points to note from this longer example are

• Collection names must be unique within an application session. Because of this

requirement, the best practice is to check for the existence of the ORDERS collection
before attempting to create it.

• Attempting to manipulate a collection that does not exist returns an error. To

avoid this, the best practice before performing operations such as truncating the
collection is to check for its existence first.

Again, though, more often than not, developers tend to use the simpler call shown in Listing 10-4
over the more granular ones shown in Listing 10-5 unless there is a compelling need for the finer control.

Adding and Removing Data from Collections
Once the collection is initialized within an application session, the APEX_COLLECTION API offers
numerous ways to manipulate its contents . We will explore the two simple methods from the Sample
Database Application here in this section and, later, we will explore some more advanced techniques.
After selecting a customer name from page 11 of the Sample Database Application and clicking the
Next> button, the user is taken to page 12. From this page, clicking the Add> buttons on the left side of
the screen or the subsequent X links on the right side, respectively, adds or removes members from the
current Order collection. Before examining these actions, it is important to understand what exactly a
collection member is.
As mentioned earlier, APEX collections are effectively session-based tables. When examining the
APEX COLLECTIONS view in Listing 10-4, you saw that collection rows have a very specific format with the
VARCHARs first, large object types next, and so on. A collection member is simply a row in the
APEX COLLECTIONS view, and it will always conform to the structure of the APEX COLLECTIONS view. The
needs of the Sample Database Application are simple and so it suffices to store the collected data into
the first few member columns of the collection even though those columns are VARCHAR2s and some of
those member attributes are actually numbers. The Sample Database Application uses the Before
Header PL/SQL process from page 12, “Add Product to the ORDER Collection”, shown in Listing 10-6.

Listing 10-6. Adding a Member to the ORDER Collection

for x in (select * from demo product info where product id = :P12 PRODUCT ID)
loop
apex collection.add member(p collection name => 'ORDER',
p c001 => x.product id,
p c002 => x.product name,
p c003 => x.list price,