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

adjustments in a new collection. Listing 10-12 shows how you can use CREATE COLLECTION FROM QUERY to
quickly populate a new collection in one command. To implement the code in your application, add an
“Adjust Order” button to page 14 that, upon clicking, runs this code as an After Submit PL/SQL process,
also on page 14, while branching to a new page 30, which we will define shortly.
Listing 10-12. Storing Query Contents into a Collection
declare
l sql varchar2(32000) := null;
begin
--
-- define query
--
l sql := 'select product id, unit price, sum(quantity) '||
' from demo order items '||
' where order id = '||:P14 ORDER ID ||
' group by product id, unit price';
--
-- call out to api for collection creation
--
apex collection.create collection from query (
p collection name => 'ORDER ADJUSTMENTS',
p query => l sql);
end;
A quick glance at this code reveals that the CREATE COLLECTION FROM QUERY procedure simply takes in
a collection name and a query string. Using that information, it creates a collection with the name
provided containing the results of the query passed in.
For our example, this approach is certainly sufficient, but for scenarios with larger amounts of data,
APEX provides the CREATE COLLECTION FROM QUERY B procedure. This procedure, too, creates a collection
with the query results using the collection name provided; however, it populates this collection behind
the scenes using bulk SQL operations to achieve its result more quickly. Listing 10-13 shows the same
query from Listing 10-12 being used with the CREATE COLLECTION FROM QUERY B procedure to create the
ORDER ADJUSTMENTS collection.
Listing 10-13. Storing Query Contents into a Collection Using Bulk Operations and Bind Variables
declare
l sql varchar2(32000) := null;
l item names apex application global.vc arr2;
l item values apex application global.vc arr2;
begin
--
-- clear collection
--
if apex collection.collection exists('ORDER ADJUSTMENTS') then
apex collection.delete collection('ORDER ADJUSTMENTS');
end if;
--
-- define query