Introduction

The Interactive Grid is a feature-rich component that facilitates handling tabular data in APEX. I frequently use a read-only Interactive Grid (IG) to allow users to select specific records from a list for further processing in PL/SQL and SQL. This post will show an example of selecting one or more business entities from an Interactive Grid and then processing them in PL/SQL and SQL.

Demonstration

In my example, I am presenting a list of Entities for processing. The user must select one or more entities and click the Process Selected button. This short video clip shows the example in action:

When the button is clicked, a Dynamic Action is called, which performs the following four steps:

1. An Execute Javascript Code step gathers the selected Primary Keys (entity_id) from the IG, generates a ':' delimited string of these IDs, and populates an APEX Page Item P9_SELECTED_ENTITIES_IDS with this string

2. An Execute Server-side Code step splits P9_SELECTED_ENTITIES_IDS into a table of IDs and performs processing on them in PL/SQL and SQL

3. A Refresh step refreshes the IG to remove the processed records from the IG

4. An Execute Javascript Code step shows a message confirming processing is complete

JavaScript Utility Function

Because I use this approach frequently, I have created a JavaScript utility function to collect selected IDs from an IG and populate an APEX page item with them. Hopefully, the comments in the code below explain what it does.

Note that I have created a namespace for the JavaScript source file so that it does not collide with any other JS that may be referenced in my APEX Application.

I uploaded the JS file to Static Application Files in my APEX Application.

I can then reference the JS file on an APEX page as follows:

Finally, the JS function can now be called from a Dynamic Action in this APEX page as follows (more on this below):

igUtil.selectedPKs("customers", "P9_SELECTED_ENTITIES_IDS", "Please select at least one entity!");

Interactive Grid Configuration

Now that we have our utility JS function, we can configure the Interactive Grid.

Region Configurations

Start by creating an IG region. I have referenced my table CNQA_BUSINESS_ENTITIES and added a filter status_code = 'PENDING' so that only un-processed entities are shown in the IG.

We need to enter the Static ID customers for our region so we can reference it from JavaScript.

Region Attribute Configurations

We need to make the IG editable (so we get a selection checkbox), but as we don't need to update or save any values in the IG, we can un-check Add Row, Update Row, and Delete Row. Finally, we can also un-check Save Button. This will leave us with the selection checkbox but effectively make the IG read-only.

Column Configurations

Next, we need to identify a primary key; in my case, it is ENTITY_ID . We are going to capture the selected primary key values for processing.

Note: If you selected multiple columns for a primary key (i.e., a composite key), then the JS Function will return ':' delimited arrays of primary key values. This complicates parsing and utilizing the values in SQL and PL/SQL.

With single Primary Key ENTITY_ID, we get this:
100:101
If we had two columns in the IG identified as primary keys, the function will return something like this:
["100","ABCDEF"]:["101","GHIJ"]

Next, we can remove the APEX$ROW_ACTION column; this removes the burger menu column from the IG. We can also ensure that APEX$ROW_SELECTOR is set to Enable Multi Select.

Dynamic Actions

Now, we can write the code to capture the selected IDs and process them from SQL and PL/SQL. In my example, I have a Dynamic action called Process Selected Entities which fires when the user clicks the Process Selected button:

DA Step 1 - Collected Selected IDs

In the first Execute JavaScript Code step, we call our utility function to generate a ':' delimited list of selected ENTITIY_IDs and return them to the page item P9_SELECTED_ENTITIES_IDS . The utility function igUtil.selectedPKs (described above) does most of the heavy lifting for us.

if (!igUtil.selectedPKs("customers", "P9_SELECTED_ENTITIES_IDS", "Please select at least one Entity!"))
  {
    // The function will return FALSE if the user does not select at least one entity. apex.da.cancel() will stop the subsequent steps in the Dynamic Action from running.
    apex.da.cancel();
  }

Note the parameters passed to igUtil.selectedPKs

• "customers" is the Static ID of my Interactive Grid Region

• "P9_SELECTED_ENTITIES_IDS" is the page item that the ':' delimited string of selected entity Ids will be returned to

• "Please select at least one Entity!" is the message I want to be displayed to the user if they do not select at least one Entity from the IG

DA Step 2 - Process Selected IDs

In the second Execute Server-side Code step, we call PL/SQL code to do something with the selected IDs. In real life, this logic should be inside a PL/SQL package.

DECLARE
  lt_entity_ids   apex_t_number;
  l_entity_id     cnqa_business_entities.entity_id%TYPE;
BEGIN
  -- Turn the ':' delimeted string into a PL/SQL Array.
  lt_entity_ids := apex_string.split_numbers(:P9_SELECTED_ENTITIES_IDS,':');
  -- Loop through the PL/SQL Array.
  FOR i IN 1..lt_entity_ids.COUNT() LOOP
    -- Get the Entity ID for the current iteration.
    l_entity_id := lt_entity_ids(i);
    -- Do something with the Entity ID.
    UPDATE cnqa_business_entities
    SET    status_code = 'PROCESSED'
    WHERE  entity_id   = l_entity_id;
  END LOOP;
END;

Note: Remember to include P9_SELECTED_ENTITIES_IDS in the Items to Submit attribute.

💡 Instead of the above, we could also use MEMBER OF and apex_string.split_numbers to process the selected IDs in one SQL statement (which is much more efficient).

BEGIN 
  -- Process all rows in one SQL Statement 
  UPDATE cnqa_business_entities 
  SET status_code = 'PROCESSED'
  WHERE entity_id MEMBER OF (SELECT apex_string.split_numbers (p_str => :P9_SELECTED_ENTITIES_IDS, p_sep => ':') FROM dual);
END;

DA Step 3 - Refresh IG

This step refreshes the IG so that processed records are removed from the list.

DA Step 4 - Complete Message

Finally, we can call apex.message.showPageSuccess to show a confirmation message to the user that the processing is complete.

Preserving Selections Across Pages

If you have Pagination Type set to Page (screenshot below), you will notice that your row selections are not preserved across pages.

You can fix this by entering the following in the Initialization JavaScript Function property of the Interactive Grid.

function(config) {
  config.defaultGridViewOptions = {
    persistSelection: true
  };
  return config;
}

Security

As you may have noticed, the item in my example which collects the IDs P9_SELECTED_ENTITIES_IDS is a hidden and unprotected page item.

This means that its value can be manipulated by a user in the browser and that value would then be submitted for processing by the Process Selected IDs Dynamic Action described above. You must protect against this by ensuring any SQL you write using the IDs contains additional logic to restrict the rows it can act upon.

Essentially, only the rows you showed in the IG to start with should be updateable by the process.

So, maybe you could re-write this:

UPDATE cnqa_business_entities
SET    status_code = 'PROCESSED'
WHERE  entity_id   MEMBER OF (SELECT apex_string.split_numbers(p_str => :P9_SELECTED_ENTITIES_IDS, p_sep => ':') FROM dual);

as this:

UPDATE cnqa_business_entities
SET    status_code = 'PROCESSED'
       -- Only eliegible records were shown in the IG.
       -- Only allow update of eligible records.
WHERE  status_code = 'ELIGIBLE'
AND    entity_id   MEMBER OF (SELECT apex_string.split_numbers(p_str => :P9_SELECTED_ENTITIES_IDS, p_sep => ':') FROM dual);

Fetching all Grid Records

Sometimes, you need to force APEX to fetch all records from the DB in the Interactive Grid model. This allows users to Select All Records using the Select All checkbox. To do this, add the following code to your page's ‘Execute when Page Loads’ property.

apex.jQuery(window).on('theme42ready', function() {
  // Change my-ig to the Static ID for your Grid.
  var grid    = apex.region('my-ig').call('getViews','grid');
  var model   = grid.model;
  model.fetchAll(function() {});
});

Note: Use this with caution. For example, fetching 10,000 records from the database to your browser is probably not a good idea.

Conclusion

Being able to process user-selected records is a common design pattern in APEX. There is currently no configuration-only option to capture user-selected records from an Interactive Grid. This post shows you how you can achieve it with only four lines of JavaScript. I hope you find this useful.

🔗 Read More

- 🩳 [APEX Shorts](https://blog.cloudnueva.com/series/apex-shorts)

- #️⃣ [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)