by Raj Mattamal
When writing applications, developers often need a way to store an unknown number of items in a temporary structure. The most pervasive example of this is the online shopping cart. When a user browses an online store, developers don’t know the number of items the user intends to purchase. To address this, application developers use collections to store these variable pieces of data.
The term collections itself is a rather general one. Many, if not most, programming techniques offer some method of storing variable collection–type data, and terms such as arrays, sets, and lists are common across them. In Oracle PL/SQL, the need to store collection data is most commonly met using constructs such as nested tables, varrays, and associative arrays. Unfortunately, these constructs aren’t generally useful across pages of Oracle APEX applications because they tend to persist for the life of the given database session, whereas APEX page views can span multiple database sessions.
To address this need to temporarily manage an unknown number of items in an application user session that might span multiple database sessions, Oracle APEX offers APEX collections. Much in the same way that APEX manages the session-related information stored into page- and application-level items, APEX collections allow developers to temporarily store variable amounts of data within a user session without burdening the developer with the mechanics of session state management. What follows is an exploration of the need for collections, some common use cases, and tips and tricks that use collections.
When to Use APEX Collections
As already mentioned, APEX collections provide application developers an easy way to store variable amounts of data. When discussing this in the context of database applications, people often ask why the information shouldn’t be stored in temporary tables. The answer to this is simple: APEX web applications are ultimately stateless, and each page view is generally a distinct database session. As temporary tables do not persist beyond the current database session, the data gathered from one page view or process in an APEX application won’t be readily available in subsequent ones. It is logical then to consider using regular database tables to store collection data. In fact, APEX collections are stored in regular database tables, but the developer does not need to be aware of this. You will take a closer look at the back-end mechanics of collections later; for now, you can think of APEX collections almost as regular tables that are associated with the session of the user currently logged into the application. What makes collections an excellent choice for storing temporary data in APEX applications is that APEX manages the session state of the collections as users click through multiple page views, and by extension multiple database sessions, without the developer having to mind the underlying concepts. To appreciate this, it is worthwhile to take a quick look into this extra overhead that APEX manages behind the scenes.
Session State Management: A Quick Overview
Most conventional web application architectures are inherently stateless. Simply put, this means that the data is not retained between subsequent executions. To allow for more complex applications, though, many web architectures have mechanisms to retain or rejoin to session data so that processing can occur across page views in what amounts to a single application session or user session.
Oracle APEX approaches this by using a session cookie stored on the user’s browser. When a user first instantiates a session in an APEX application, a session cookie is placed on the user’s computer. When that user attempts to access the same application again with a valid cookie, APEX recognizes the returning user and makes that user’s session data available. A simple example of how this would be useful is a multiple page survey that collects information from a user.
Imagine a user, John, is entering information into a web survey written in APEX. When John enters his name into an item on the first page, P1_NAME, and then clicks to the next, his name appears at the top of the second page in the form of static text, for example, “John’s Survey Responses, Page 2.” APEX was aware that it was John accessing the second page and not some random user because, behind the scenes, it recognized the valid session cookie and made that session’s value of P1_NAME available to the application. It is certainly possible to manage this nontrivial logic on your own, but APEX does this incredibly efficiently and seamlessly for you. Later, when the user’s session has been inactive for a significant period of time, APEX even purges the older data so that unnecessary values like P1_NAME aren’t store beyond their usefulness.
Session State–Managed Tables
Similar to the way APEX handles session data for page- and application-level items, APEX collections allow developers an easy way to share data across page views without having to check for cookies and rejoin session data. In this sense, APEX collections can be thought of as tables that automatically provide session state management. With this concept in mind, you can take a deeper look into the logical workings of the traditional shopping cart example that so often is associated with APEX collections.
Logically Walking Through a Web Shopping Cart Implementation
If you built an APEX application to serve as a store for online purchases, you would need a way to track some basic information about the items being purchased by users as they browse your store. To have this data available at checkout time, you need to tie the data to the users’ session in some way. Earlier, I walked through the example of a single APEX item, P1_NAME, whose data persists across user session page views, and this might meet your needs if your customers were allowed to buy only one book per store visit—however, that’s an unlikely rule. To store information about the unknown number of items your customers might buy, you would want to create a collection storing information such as the product description, price, and quantity purchased. This way, as the user clicked between pages of your online store, APEX would automatically recognize returning users and make their shopping cart available each time. At checkout, you could take the information gathered in the collection, process it as necessary, and store the relevant details in some sort of Orders table. To avoid clutter, you could then drop the collection—but if you overlooked this advisable course, APEX would handle the purging for you at a later time.
A Look Under the Covers
Before actually discussing APEX collection use cases and techniques, it is worthwhile to take a quick peek into the APEX engine to see how collection data is stored and made available to developers. As mentioned earlier, APEX collections are in fact stored in regular database tables. Once this data is stored, users can then access it via the publically available APEX_COLLECTIONS synonym, which in turn points to the WWV_FLOW_COLLECTIONS view in the APEX engine schema. Ultimately, it is only the APEX_COLLECTIONS view and the associated APEX_COLLECTION API that developers need to familiarize themselves with. Still, in the interest of thoroughness, let’s take a deeper dive.
Private Collections Objects Inside the APEX Engine
The APEX engine is effectively a single schema in the database with some helper schemas to serve specific purposes. It is within this main schema that APEX stores all its session data. Collection data is no exception to this. Specifically, APEX collections are stored entirely in two tables: WWV_FLOW_COLLECTIONS$ and WWV_FLOW_COLLECTION_MEMBERS$. A quick glance at the column names in Listing 15-1 shows you that the WWV_FLOW_COLLECTIONS$ table doesn’t store application data. Instead, it stores the information APEX needs to handle the session state management functionality discussed earlier.
When you take a look at the second collections table’s description in Listing 15-2, you immediately see what appear to be placeholder columns for data such as C001 and C002 for text, N001 for numbers, and D001 for dates. It is into this table that APEX stores its collection data, but, again, it uses WWV_FLOW_COLLECTIONS$ to expose the right collected data to the right sessions in the right applications.
Public Collections Objects Inside the APEX Engine
As already said, APEX exposes its collection data to developers in the form of the APEX_COLLECTIONS public synonym. A quick glance at Listing 15-3 shows that the view associated with APEX_COLLECTIONS, WWV_FLOW_COLLECTIONS, closely mirrors the columns in the WWV_FLOW_COLLECTION_MEMBERS$ table.
So, although APEX developers do not need to be aware of the underlying tables in the main APEX engine schema, familiarizing yourself with the structure of the exposed APEX_COLLECTIONS view is helpful when developing. The most important points to glean from this are
- The data in APEX_COLLECTIONS is effectively keyed off by the COLLECTION_NAME and SEQ_ID columns.
- The remaining columns of the APEX collections infrastructure allows for the storing of multiple data types.
- Varchar2
- BLOBs
- CLOBs
- XMLTYPEs
- Numbers
- Dates
- The number of values that can be stored per row is limited to the number of columns exposed in the view. (Workarounds for this will be discussed later in the chapter.)
The other publically accessible part of the APEX collections infrastructure is the PL/SQL API, APEX_COLLECTION, which you will explore in the next section.
Getting Started with Collections
With a basic understanding of the APEX collections infrastructure, you are now ready to start using them. To do so, let’s use the example that comes available with every APEX instance, the sample database application demonstration.
Note If the sample database application isn’t already available in your APEX workspace, it can quickly be installed by clicking the Create icon atop the Application Builder home, clicking Packaged Applications, and then selecting the sample database application.
Much like the shopping cart example, the sample database application allows users to create an order form for a variable number of products. Once the desired number of products has been selected, the user can place the order, which effectively moves the collection data to the more permanent tables stored in the application schema. To achieve this, a few key APEX_COLLECTION API calls are used, and these are the first methods to understand when using collections. The application actually performs these functions in both its desktop and mobile interfaces, so I will walk you through these methods in the application’s mobile interface next.
Initializing a Collection
Before using a collection, it is necessary to define it in the context of the current application session. This can be thought of as initialization. To access the mobile page in the sample database application where the collection is first referenced, log in, click the Mobile option in the top navigation bar, and then click the Monthly Orders option in the panel menu. Once there, clicking the Create button takes the user to page 216 of the application, and this is where your collection is first referenced. Specifically, a new collection is initialized in a PL/SQL Anonymous Block process that fires Before Header when the user first comes to page 11 (see Listing 15-4).
As shown in Listing 4-4, this initial call to the APEX_COLLECTION API offers developers a means from one API call to either clear out any collection called ORDER or, if one does not yet exist, to create it. It is important to note here that the APEX_COLLECTION API indeed offers separate procedures to create and truncate collections, but most APEX developers tend to use the create_or_truncate call to eliminate the need to check for the collection’s existence before resetting it. Listing 15-5 shows the somewhat more cumbersome way to achieve the same collection initialization as in Listing 4-4.
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 4-4 over the more granular ones shown in Listing 5-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. You will explore the two simple methods from the sample database application in this section, and then you will explore some more advanced techniques.
After selecting a customer name from page 216 of the sample database application and clicking the Next button, the user is taken to page 217. From this page, clicking links on the right side of the screen adds or removes members from the current ORDER ollection. 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 4-4, you saw that collection rows have a 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 217, Add Product to the ORDER Collection, shown in Listing 15-6.
However, if you wanted, for example, to later perform aggregate functions on the number values stored, it would be better to store the numeric values into the NUMBER columns. This is done by simply passing the numeric value into the ADD_MEMBER numeric input parameters rather than the VARCHAR2 ones, as shown in Listing 15-7.
Upon further examining this PL/SQL block, you can see that your collection will end up storing one row for every item added to the order, regardless of quantity. Again, although this suffices for the needs of the sample database application, there might be cases where you would want to update the quantity column for repeated product selections rather than simply inserting a new collection row each time. Doing so affords you the opportunity to use the UPDATE_MEMBER method, as shown in Listing 15-8.
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 8-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.
Deleting 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 217 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 15-9.
This simple DELETE_MEMBER procedure works fine for the default implementation within the sample database application, but it would not suffice for the more advanced implementation 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 15-10, this is accomplished with the same procedure call used in the more advanced implementation of the addition of new members.
Using the Collection Contents
After a user is done selecting products and clicks the Place Order button on that screen, a page-level PL/SQL process is executed that does the two final pieces of the initial collections walk-through: it makes use of the collected information and deletes the APEX collection to free up resources. You have already seen examples of querying the collection contents in both of the more advanced order management code sections earlier, and, as you can see in Listing 15-11, this final use of the ORDERS collection is no different. With the collection contents stored more permanently in a table in your schema, DEMO_ORDER_ITEMS, you are able to clear out the contents using the TRUNCATE_COLLECTION call.
A final detail to observe from the walk-through is the simple validation that is executed when the user clicks the Place Order button. Specifically, note the validation a function from the APEX_COLLECTION API called COLLECTION_MEMBER_COUNT, as shown in Listing 15-12. As the name suggests, this function returns the number of items (rows) stored in a given collection.
The validation could have equivalently queried the count of relevant rows from the APEX_COLLECTIONS view, but using the provided API function keeps the code short and tidy.
Exploring Another Use Case
Another common scenario in which APEX developers use collections in their applications is to store check box values that are checked in paginated reports. The reason for this is that check boxes rendered inline in a report are generally created using the APEX_ITEM API. Form elements rendered using this API do not support the same notion of session state as regular APEX page- and application-level items. Instead, the elements are rendered, and the developer is required to programmatically store the submitted values. Given this, when report rows are paginated, APEX won’t automatically remember which rows were checked on a prior screen if the user has navigated away. In the case where a user is presented with check boxes in each row of a long report, the checked values will be forgotten each time the user clicks the report’s Previous and Forward navigation options. Put simply, if the user checks a row on the first page of the result set, clicks Next to view the next set of records, and then clicks Back to return to the beginning, the user will find that the originally checked rows are no longer checked. For this reason, developers save checked values in a collection so that APEX has the values available to it when the user navigates back to prior pages. It is this association between the user’s session and the user’s checked values that makes APEX collections ideally suited to this purpose.
Expanding the Example
To better understand how to save check box values implemented using collections, let’s modify the sample database application to allow users to delete orders in bulk directly from the main Orders page. Specifically, you will add check boxes to each row of the Orders listing as well as a new Delete Checked button to page 4 of the application, which will allow users to delete the rows checked. As the user clicks each check box, you’ll store the associated values to a collection to be processed upon the user clicking the Delete Checked button.
Setting Up the Example
Before getting into collection processing, let’s set up the UI for this expanded example. Start by adding a new button to page 4 of the application within the Breadcrumbs [Global Page] region. When creating the new button, accept all the default values except for the attributes shown in Listing 15-13. Doing so will make the new button match the Enter New Order button already on that page.
Shortly, you’ll modify the query in the Orders region to render the check boxes, but let’s first add some of the supporting pieces to manage the new collection. Start by adding three application-level processes to perform the PL/SQL used to manage the checked values. To create the first process, navigate to the Application Processes section within the Shared Components section of the sample database application. Next click the Create button, enter a Name of store_checked_order, select “On Demand: Run this application process when requested by a page process” for Point, and click Next. Enter the code shown in Listing 15-14, click Next, and then click Create Process.
The PL/SQL in the new process will be called from JavaScript when users check or uncheck orders on page 4. Although this code uses many of the same APEX_COLLECTION calls you’ve already reviewed, it brings them together into one block to intelligently add or remove ORDER_ID values into the new collection. Reviewing the comments within the code, you can also see that the process will create the collection if it’s not already there. This logic is included so that you’re guaranteed to have a place to store the ORDER_ID even on the first click. As an additional note, it would be slightly more performant to have the calls to apex_collection.add_member use the numeric p_n001 parameter instead of the varchar2 p_c001 parameter that’s shown because values passed into p_n001 are stored in the numeric collections column, N001, saving you from having to rely on implicit conversions later. For simplicity later in the example, however, you’ll have the code use the first available collection column, C001. (Please see Listing 3-3 for a description of these columns in the APEX_COLLECTIONS view.)
Beyond allowing users to check individual check boxes, the example will include a check box at the top of the report to allow users to check or uncheck all the rows of the report. The two remaining application processes you will create support the “uncheck all” and “check all” functionalities, respectively.
Create a new application process exactly as you did for store_checked_order, but call this one unstore_all and have it use the PL/SQL, as shown in Listing 15-15.
Notice here that even though the purpose of this process is to clear out any values stored in the CHECKED_ORDERS collection, you are using the create_or_truncate API call. This is a common technique used by APEX developers because it accomplishes the goal of clearing out the collection while not having to check first for its existence. If you had used the more intuitive delete_collection procedure, there’s a risk that APEX would return an error if for some reason the collection hadn’t already been created. To get around this possible error, a lengthier way to code the unstore_all process is shown in Listing 15-16.
The last application process you need to create effectively replaces the collection contents with all available ORDER_IDs when the user checks the Check All check box. Create the new application process the same way you did for the prior two processes, but call this one store_all and have it use the PL/SQL shown in Listing 15-17. (Please note that this process calls the APEX_IR API to fetch the last version of the report shown on page 4. This is done in case the user has filtered the rows displayed before checking the Check All check box. By calling the APEX_IR API, you will know exactly what order numbers were presented on the screen when the user clicked Check All.)
This final application process introduces a powerful APEX_COLLECTION procedure, create_collection_from _query_b. As the name suggests, the procedure takes in a query and stores its result set into a new collection. It’s worth mentioning that the APEX_COLLECTION API also offers a simpler CREATE_COLLECTION_FROM_QUERY procedure, but it is advisable to call CREATE_COLLECTION_FROM_QUERY_B whenever possible because it takes advantage of bulk SQL operations for better performance.
With the three application processes created, you need to put some logic on page 4 directly and add the check boxes to the report to complete the expanded example. The logic needed on page 4 comes in the form of two JavaScript snippets to call your application processes and one PL/SQL process that actually deletes the checked orders. You’ll start by adding the JavaScript snippets to the page header. Within the APEX builder, navigate to the definition of page 4, and enter the text shown in Listing 15-18 in the Function and Global Variable Declaration field with the JavaScript region of the page-level attributes.
Reviewing the script in Listing 18-18, you can see that it contains two pieces: the JavaScript function, storeCheckedOrder, which calls the store_checked_order application process via an XMLHttpRequest, and a jQuery-based JavaScript snippet that calls the other two application-level processes you added depending on whether the user has checked or unchecked the Check All check box. Please note that many APEX developers prefer to handle the JavaScript logic over using APEX dynamic actions. You’re using regular JavaScript in this example so as to reduce the number of steps.
Now you are ready to add the final piece of logic to page 4, which is the process that actually deletes the selected orders. This logic will be implemented in the form of a PL/SQL process that fires when the user clicks the DELETE_CHECKED button you added earlier. To add the process, enter the PL/SQL shown in Listing 15-19 into the source of a new process on page 4 that has the attributes shown in Listing 15-20.
Next, let’s add check boxes to the report. To do so, you’ll simply add a new column to the query used in the Orders region. As mentioned earlier, check boxes to be rendered within reports are best added using the APEX_ITEM API. In this case, you’ll use the CHECKBOX2 function and indicate that it should call our storeCheckedOrder JavaScript with each click. Beyond calling storeCheckedOrder, the other consideration for the check boxes is to have them remember previously checked values. This is actually the heart of the example in that you need to refer to your collection for those saved checked values in case the user has navigated off the page after making selections and comes back. As collection values are easily accessible from the apex_collections view, you simply need to join it into your query and refer to its stored ORDER_IDs when calling CHECKBOX2. Both of these changes are reflected in Listing 15-21, so replace the current source of the Orders report region with the query shown in the listing.
The last detail necessary to complete the example is to adjust the column header for the new checkbox column so that it cleanly renders a Check All check box. To do so, navigate to the column attributes screen for the new DELETE_CHK column and replace the Heading attribute with the text shown in Listing 15-22.
Disable all the Enable Users To options presented on the DELETE_CHK column header attributes screen. Specifically, this entails toggling the selectors for Hide, Sort, Filter, Highlight Control Break, Aggregate, Compute, Chart, Group By, and Pivot to No. Additionally, set the Escape Special Characters attribute to No. Disabling these extra features allows the column header to cleanly render without advanced interactive report functionality interfering.
Finally, set the “Escape special characters” option to No.
With the setup complete, your page 4 should resemble Figure 15-1. Your sample database application now uses a collection called CHECKED_ORDERS to store ORDER_IDs and delete them in bulk. After adding a few orders, try checking some values on one page of the result set, navigate to the next, and notice your values are still checked when you return to the prior page. Notice, too, that if you check the Check All check box on one page of the resultset and navigate to the next, those order are checked as well. All of this is made possible by using an APEX collection to remember which orders were checked.
Figure 15-1. Adjusted order form
Summary
Offering the ability to extend APEX’s native session state management functionality into session-based tables, APEX collections still tend to be surprisingly underused among beginner and even intermediate APEX developers. Using the techniques described in this chapter, I hope that more developers will take advantage of this powerful functionality available within APEX applications. Although I have covered only the most conventional uses of APEX collections here, a quick search on the Web will reveal developers thinking outside of the proverbial box to use collections to bring new and even unforeseen power to their APEX applications.