Taking Up CollectionsBy Mike Hichwa
Use collections to manage session state in Oracle Application Express.
Oracle Application Express is an easy-to-use application development tool. However, sometimes even simple applications can get a bit tricky.
That is exactly what happened when a former colleague of mine asked for some assistance in building an application to help him manage his wholesale shoe business. He had been using a spreadsheet to manage orders, but he wanted to move to the Web and use an Oracle database. He had been dabbling with Oracle Application Express but didn't know how he could duplicate his spreadsheet.
He wanted his salespeople to be able to order one or more shoe styles for retailers (his customers) by using a simple grid specific to each product, just as they were doing at the time with the spreadsheet. As a one-time Oracle employee, my former colleague also wanted to store the data in properly normalized tables.
The solution described in this column, Matrix Order, is available at oracle.com/technetwork/issue-archive/2006/06-nov/o66browser-132532.zip. You can download a single SQL file, install the application at apex.oracle.com or in your local Oracle Application Express 2.2 instance, and see how everything works. The single SQL file creates the application, tables, sequences, and sample data required to illustrate the solution.
The solution is a three-step wizard, Create Order, that lets salespeople identify the customer (step 1) and then select a product style and enter quantities for each available color and size (step 2). In step 3, the complete order appears, allowing confirmation or cancellation.
Step 2 of the wizard, shown in Figure 1, collects order details in an efficient, intuitive way: Salespeople can select a product from the product list and have a grid populated automatically with color, price, and size information.
Data entered and preserved across page views can be transformed into a table, MATRIX_ORDER_ITEMS, that has one row per cell in the grid. The application uses collections to manage a grid of data until the user gives final approval, at which point the data in the collection is transposed and stored in application tables.
A collection is an Oracle Application Express utility that can manage a two-dimensional session state—basically a table of data. The APEX_COLLECTIONS API offers developers methods for populating, querying, and updating collections.
In many ways, collections are similar to tables. The big difference is that they are specific to an Oracle Application Express session, so collections are (conveniently) cleaned up when your session is purged. Collections come in handy whenever your application needs to maintain an arbitrary number of attributes, such as a "shopping cart" of attributes you load into your application tables at checkout.
This application collects the order data via the wizard, enabling the salesperson to preview the order on a different page and return to the prior page and make changes, if necessary. When the Create Order wizard finishes, the contents of the collection are used to populate the MATRIX_ORDER_ITEMS table; the collection is then emptied until the next session begins.
Creating a Collection
When you initiate the Create Order wizard, a before-header PL/SQL page process (shown in Listing 1) seeds the collection with all product styles and colors, along with a column for each available size. The page process deletes the collection if it already exists and creates the matrix necessary to manage the data entry screen.
Code Listing 1: "Reset collection" before-header page process
if apex_collection.collection_exists(p_collection_name=>'MATRIX') then apex_collection.delete_collection(p_collection_name=>'MATRIX'); end if; apex_collection.create_collection_from_query( p_collection_name=>'matrix', p_query=>'select p.pro_style, p.pro_color, p.pro_unit_price, max(decode(p.pro_colour,'S',0,null)) s, max(decode(p.pro_colour,'M',0,null)) m, max(decode(p.pro_colour,'L',0,null)) l, max(decode(p.pro_colour,'XL',0,null)) xl from matrix_products p group by p.pro_style, p.pro_colour, p.pro_unit_price order by 1,2,3');
select c001, c002, c003, nvl(c004,'0') c004, nvl(c005,'0') c005, nvl(c006,'0') c006, nvl(c007,'0') c007 from apex_collections where collection_name = 'MATRIX' and c001 = :p3_product
Note that APEX_COLLECTIONS is a public view with columns C001 through C050, although this example uses only seven columns.
The contents of the collection—MATRIX—are populated from the query.
C001 is the product style, C002 is the color, C003 is the unit price, and C004 through C007 contain the values for the quantity ordered: sizes S, M, L, and XL.
After creating the report and adjusting the column headings, I modified the last four columns (S, M, L, and XL) to be updatable. I did this by clicking the column in the Report Attributes tab of the Edit Region page in the Oracle Application Express Application Builder. To make the last four columns updatable, I simply changed the Display As attribute to Text Field and set Element Width to 8.
Saving Report Columns into a Collection
The application must allow users to move around on the pages of the wizard in any way they want to, entering different quantities for multiple products; previewing the in-process order; and returning to a prior page and making changes, if necessary. That means that each time the user leaves the Order Entry page, the application must save changes to the collection. To handle this, the application uses an after-submit PL/SQL page process (shown in Listing 2) that updates order values in the collection.
Code Listing 2: "Update collection" after-submit page process
declare c pls_integer := 0; begin for c1 in ( select seq_id from apex_collections where collection_name = 'MATRIX' and c001 = :p3_product_on_load order by seq_id) loop c := c+1; apex_collection.update_member_attribute (p_collection_name=> 'matrix', p_seq=> c1.seq_id,p_attr_number =>4,p_attr_value=>wwv_flow.g_f01(c)); apex_collection.update_member_attribute (p_collection_name=> 'matrix', p_seq=> c1.seq_id,p_attr_number =>5,p_attr_value=>wwv_flow.g_f02(c)); apex_collection.update_member_attribute (p_collection_name=> 'matrix', p_seq=> c1.seq_id,p_attr_number =>6,p_attr_value=>wwv_flow.g_f03(c)); apex_collection.update_member_attribute (p_collection_name=> 'matrix', p_seq=> c1.seq_id,p_attr_number =>7,p_attr_value=>wwv_flow.g_f04(c)); end loop; end;
The PL/SQL page process loops through all the rows of the MATRIX collection, updating each of the size columns (S, M, L, XL). The value of the first updatable column in the first row of the report can be accessed by use of the WWV_FLOW.G_F01(1) array value; the second updatable column of the first row is WWV_FLOW.G_F02(1).
When a salesperson changes a product in the list, the report queries the details of the new product and creates a new matrix with no values.
As the product changes, the existing values (for the previously displayed product) need to be saved for the previously saved product, not for the newly selected product. A hidden item, P3_PRODUCT_ON_LOAD, ensures that this happens correctly: The value of this hidden item is set to the value of the selected product by use of an after-header computation.
Transforming Collection Members into Table Rows and Columns
So far I've shown how to populate, query, and update a collection. Now, the last step is to transform the collection into an INSERT statement for the MATRIX_ORDER_ITEMS table.
Step 3 of the wizard, shown in Figure 2, queries the collection to display the current orders and provides a Confirm Order button. Clicking the button initiates the on-submit page process (shown in Listing 3), which saves the collection to the table by looping through the collection and inserting a row into the MATRIX_ORDER_ITEMS table for each cell in the collection.
Code Listing 3: "Place order" on-submit page process
declare l_order_id number; l_product_id number; l_order_date date := sysdate; procedure insert_order_item (p_qty in number, p_style in varchar2, p_size in varchar2, p_color in varchar2, p_price in number) is begin if p_qty > 0 then select max(pro_id) into l_product_id from matrix_products where pro_style=p_style and pro_size=p_size and pro_colour=p_color; insert into matrix_order_items (oi_qty,oi_pro_id,oi_ord_id,oi_unit_price) values (p_qty,l_product_id,l_order_id,p_price); end if; end; begin select matrix_prod_order_seq.nextval into l_order_id from dual; insert into matrix_orders (ord_id,ord_date,cust_id) values (l_order_id,l_order_date,:p6_customer); for c1 in ( select c001 style, c002 color, to_number(c003) price, to_number(c004) s_qty, to_number(c005) m_qty, to_number(c006) l_qty, to_number(c007) xl_qty from apex_collections where collection_name = 'MATRIX') loop insert_order_item (c1.s_qty,c1.style,'S',c1.color,c1.price); insert_order_item (c1.m_qty,c1.style,'M',c1.color,c1.price); insert_order_item (c1.l_qty,c1.style,'L',c1.color,c1.price); insert_order_item (c1.xl_qty,c1.style,'XL',c1.color,c1.price); end loop; :p17_order_number := to_char(l_order_id); apex_collection.delete_collection (p_collection_name => 'MATRIX' ); end;
At the end of the loop, the process populates page items (including order numbers and totals) that are used in the "Order Processed" confirmation message displayed to the user. After this PL/SQL page process executes, the ordering process is complete.
That's the process. The Matrix Order application demonstrates how to use collections to manage session data entered in a matrix format. The application stores and maintains the data across any number of product changes, and then it loads the collection into the application tables for permanent storage.
Mike Hichwa (firstname.lastname@example.org) is vice president of software development at Oracle and manages Oracle Application Express, Oracle SQL Developer, and other database development tools. He was the original architect of Oracle Application Express.