Collections – Dive Into APEX

An Oracle colleague asked how to create an editable Interactive Grid against an APEX collection. I share the solution here along with a few observations I learned while creating it.

Client Side Grid Model

An interactive grid manages an in-memory record set in the browser. This is called the model. An editable grid’s model also tracks rows the end user has added, modified, and deleted. When the user submits the page or clicks the grid toolbar’s (Save) button, the changes in the model are sent to the server for processing by the Interactive Grid – Automatic DML page process.

The client-side model uniquely identifies rows using the values of the region columns marked as Primary Key and assumes those values won’t change. For rows the user creates, it generates a temporary unique key.

Grid Primary Key Cannot Change

When the grid saves a new row, the grid-assigned temporary key gets replaced by the database-assigned key. That’s the only key-change situation the grid allows.

I had begun by using the APEX_COLLECTIONS view’s SEQ_ID as the grid’s primary key column, thinking it was a safe choice for an unchanging unique key. This wrong assumption was the gotcha I encountered when pairing the grid with an APEX collection. I learned that it’s definitely unique, but it’s not necessarily unchanging. In certain situations, the collection’s sequence ID can get reused or changed.

The more obvious way this can happen is if the developer calls any of the APEX_COLLECTION package’s procedures that affect the ordering of the collection like: SORT_MEMBERS, MOVE_MEMBER_UP, MOVE_MEMBER_DOWN, or RESEQUENCE_COLLECTION.

But what I ran into was something more subtle. Assume we use a collection for a simple shopping cart with columns:

C001 as ITEM_CODE
N001 as QUANTITY, and
D001 as NEED_BY_DATE

I ran into trouble with the following scenario:

Add “Banana” to the shopping cart grid
Click (Save) on the grid toolbar to persist that changes in an AJAX request
Delete “Banana” from the grid
Add “Cherry” to the shopping cart grid
Click (Save) on the grid toolbar again

At this point, I received an error in the JavaScript console:

Uncaught TypeError: Cannot read properties of null (reading 'serverOffset')
at Object._resequenceServerOffset (modelViewBase.min.js?v=24.2.2:4:40633)
at Object._clearChanges (modelViewBase.min.js?v=24.2.2:4:39956)
at modelViewBase.min.js?v=24.2.2:4:13563

How Collection Assigns SEQ_ID

The APEX_COLLECTION package assigns a unique sequence ID to each member as you add it. It uses the straightforward approach of using one greater than the maximum sequence ID currently in the collection, or one (1) if the collection is empty.

In the above scenario, my “Banana” row got assigned SEQ_ID = 1. Then when I deleted “Banana” and inserted “Cherry,” at save time APEX Grid Automatic DML page process performs deletes first. This removed “Banana” from the collection, leaving no members. Then the insert of “Cherry” was performed. At that time, since there were no members in the collection, “Cherry” was a assigned a sequence of one (1).

When the grid’s AJAX save request returned, it needs to consolidate the rows in its client-side model with the new data returned from the server. At that time, its in-memory row set it still has “Banana” with primary key value 1 (marked as deleted). But in the new data returned in the AJAX response, it finds a new row “Cherry” also with primary key value 1 marked as a new row. At this point the model gets confused because it assumes the primary keys never change unless it’s the grid-assigned temporary key changing to the database-assigned key value during insert.

Using a SYS_GUID Instead

The solution is to generate a unique key value and store that into the collection in one of the generic columns. Here we’ll use C002 to store a value we’ll assign using the SYS_GUID() function. By then configuring the grid to use this unchanging unique value, the above use case works fine.

Shopping Cart Package API

I created the following PL/SQL package API to encapsulate my use of the APEX Collections API behind a more meaningful set of function and procedure names related to adding items to a shopping cart:

create or replace package shopping_cart_api is
    ----------------------------------------------------------
    procedure clear_cart;
    ----------------------------------------------------------
    function add_item(
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date)
    return varchar2;
    ----------------------------------------------------------
    procedure remove_item (
        p_item_id in varchar2);
    ----------------------------------------------------------
    procedure update_item (
        p_item_id      in varchar2,
        p_item_code    in varchar2,
        p_quantity     in number,
        p_need_by_date in date);
end shopping_cart_api;

Simplifying Collection Access with a View

The shopping cart API above hides the details of working with the APEX_COLLECTION package from the rest of my application pages. A final touch was creating a database view to hide references to the APEX_COLLECTIONS generic view from my pages as well. I created the following view:

create or replace view shopping_cart_v as
select c001 as item_code,
       c002 as id,
       n001 as quantity,
       d001 as need_by_date
 from apex_collections
where collection_name = 'SHOPPING_CART'

The Solution Sample App

This let me create a grid based on the SHOPPING_CART_V view and configure the grid’s DML page process to have custom PL/SQL-based save-time logic based on the SHOPPING_CART_API package. The Grid DML page process settings appear below. Notice I’ve disabled row locking and lost update protection. Since the collection data is private to the current user session, there’s no chance another user can lock or change a row.

The complete code for the PL/SQL Code to Insert/Update/Delete looks like this:

case :APEX$ROW_STATUS
    when 'C' /* Create */ then

    :ID := shopping_cart_api.add_item(
        p_item_code    => :ITEM_CODE,
        p_quantity     =>
           apex_session_state.get_number('QUANTITY'),
        p_need_by_date =>
           apex_session_state.get_timestamp('NEED_BY_DATE'));

when 'U' /* Update */ then

    shopping_cart_api.update_item(
        p_item_id      => :ID,
        p_item_code    => :ITEM_CODE,
        p_quantity     =>
           apex_session_state.get_number('QUANTITY'),
        p_need_by_date =>
           apex_session_state.get_timestamp('NEED_BY_DATE'));        

when 'D' /* Delete */ then

    shopping_cart_api.remove_item(
        p_item_id => :ID);

end case;

Since APEX treats all page items and grid columns as strings, I use the GET_NUMBER and GET_TIMESTAMP functions in the APEX_SESSION_STATE package to get a correctly typed number and date value, respectively. These functions automatically take into account any format mask configured on the item or column if present.

Declaratively Wiring Button to Grid Action

The demo includes two pages. Page 1 has a grid with the toolbar turned off and footer turned off, forcing the save to happen using a normal page submit (Save) button. Since the (Add Item) button is part of the grid toolbar, if the toolbar is hidden the end user has no way to add a row. So I wanted to put an (Add Item) button on the page that let users add a new row to the grid.

My colleague John Snyders taught me a declarative technique for doing this by:

Assigning the grid a static ID like shoppingcart
Setting the Action of the button to Defined by Dynamic Action, and
Configuring the button’s Custom Attributes property to be
data-action="[shoppingcart]selection-add-row"

The grid region’s static ID appears in square brackets, and the action name is one of the ones defined in the Actions section of the grid’s JavaScript documentation.

This instantly made my button perform the grid’s native add row action and let me position the button anywhere on my page instead of having to enable the grid toolbar just to have the (Add Item) button.

One Final Quandry Resolved

After configuring the (Add Item) button, I was puzzled when I noticed my button label “Add Item” got changed at runtime to “Add Row“. It turns out when you declaratively “wire” a button to an action, then on page load the button label takes on the action label automatically. The grid lets you configure the label of the (Add Row) toolbar button, however that property value is only visible and editable and persisted if the grid toolbar is on. So, with no developer-overridden action label saved for the “Add Row” operation in the grid region metadata, it reverts to the default label of “Add Row”.

I solved this by setting the label of the selection-add-row action to “Add Item” in my grid region’s JavaScript initialization function using the following tiny bit of code:

function(options) {
  options.initActions = function ( actions ) {
    // Set label of "Add Row" action
    actions.lookup("selection-add-row").label = "Add Item";
  };
  options.defaultGridViewOptions = {
    // No footer please
    footer: false
  };
  return options;
}

I later learned I could do avoid writing the line of JavaScript that sets the action label by adding an additional custom attribute data-no-update="true" to my (Add Item) button. John pointed me to the Buttons section of the grid JavaScript docs that mentions this tip in a sentence that says, “If you don’t want the label, title, or icon to be updated add [this] attribute…”

Processing the Shopping Cart Contents

The sample app does not do this, but just for completeness know that your app can process the contents of the shopping cart using a PL/SQL cursor for loop. This use case offers another compelling reason to create a view to hide direct access to the APEX_COLLECTIONS view. It makes your business logic easier to read as well. Such a loop in this shopping cart example would look like:

for j in (select item_code, quantity, need_by_date
            from shopping_cart_v)
loop
   -- Reference j.item_code, j.quantity, j.need_by_date
end loop;

Downloading the Sample App

You can download the sample app from here to install in APEX 24.2 or later. It includes Supporting Objects code for the SHOPPING_CART_API package you can study further. Enjoy!

Tag: Collections

Edit Collection with Interactive Grid