Databases Reference
In-Depth Information
--
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';
--
-- populate arrays with bind variable information
--
l item names(1) := 'P14 ORDER ID';
l item values(1) := :P14 ORDER ID;
--call out to bulk create api
apex collection.create collection from query b (
p collection name => 'ORDER ADJUSTMENTS',
p query => l sql,
p names => l item names,
p values => l item values);
end;
One very significant performance improvement also employed in the above code is passing of bind
variables to the API. Using bind variables obviously allows you to take advantage of existing query plans,
should they exist, and, whenever possible, developers should strive to use them. In APEX versions prior
to 4.0, bind variables were not supported in the
CREATE COLLECTION FROM QUERY B
procedure, but
fortunately developers may preserve them in current versions.
Creating a Tabular Form to Be Validated
The button and associated process added to page 14 above allow us to use the new
ORDER ADJUSTMENTS
collection data in a new tablular form. To create this form, use the APEX Create Report wizard to create a
classic report on a new page 30 using the query shown in Listing 10-14. To complete the form's user
interface, you will also add a button to page 30 and an associated branch that returns the user to page 14.
Listing 10-14.
Report Region Query to Render Tabular Form
select p.product name,
apex item.text (2, c.c002, 5, 6 )
||apex item.hidden(1,c.c001) --product id
price,
apex item.text (3, c.c003, 5, 6 ) quantity
from demo product info p
, apex collections c
where p.product id = c.c001
and c.collection name = 'ORDER ADJUSTMENTS'
order by c.seq id
At this point, the user should be able to create an order, click the new “Adjust Order” button on page
14, and come to page 30 to view a tabular form of the current order as shown in Figure 10-2.
