APEX Interactive Grid - Processing Selected Records

UpdatedJanuary 17, 2025

Hi, thanks for stopping by! I am focused on designing and building innovative solutions using AI, the Oracle Database, Oracle APEX, and Oracle REST Data Services (ORDS). I hope you enjoy my blog.

Comments (10)

Join the discussion

Rahul Ghosh1y ago

This is the best answer I have seen so far. I loved it. It works so seemlessly even in mid-2025. I also added a "Submit Page" action after the complete message so that the page reloads and does not retain the selected records. Well I did this for my own use-case but thanks a lot! This was soooooooooo helpful <3

Javier Reyez2y ago

Great article.

Gregory Grimes2y ago

Did I miss where you created the button and placed it in the IG region?

Rahul Ghosh1y ago

He did not show that part cuz it is kinda obvious i guess. However, this solution is great, cheers!

Gregory Grimes2y ago

I always enjoy reading your posts. What I want to do as part of the processing is to generate GRANT statements. My IG query compares a list of existing grants and a list of objects needing grants. I'm planning to generate an IR with the grants statements which the user can download. Is there a better way to do this within APEX? The goal is to demonstrate to our team, rather than passing a round shared Excel files, APEX is a better way. I hate using Excel to consolidate info.

Thanks.

Nat Asuah3y ago

Hi Jon, I have been looking for a solution like this for a while and coming across your post, I thought I had struck Gold. Well, I did. Just a little comment on the "completion Message". The completion message is pop up irrespective of the outcome of the "Process Selected IDs". How do I link those events? Once again, thanks.

Jon Dixon3y ago

Hi Nat, Glad you liked the post. You could create another page item that is Hidden and Unprotected. In the 'Execute Server-side Code' step, populate that page item with a more specific message or some text that indicates that the completion message should not be displayed. In the Completion Message Javascript, put an if statement around the apex.message.showPageSuccess to either not display it or display the content of the new item. You could even use the JS API apex.message.showErrors to indicate an error occurred.

Leon Match3y ago

Jon Dixon Hi Jon, I implemented the solution described in your post, but I do have an issue - When I click on "Select All" checkbox in the header of APEX$ROW_SELECTOR column, I only get the records visible (rendered) on page. Even though the total row count, in the footer of the grid, shows the count of all rows selected, the page item gets populated only with 50 ids. When I start scrolling down and click "Process Selected" button, I start getting more ids. How do I solve this? How can I get all grid's ids when Select All is checked?

Thanks again!

Jon Dixon3y ago

Hi, for Fetch All, I recommend having a second button that says something like 'Process All'. Clicking this button would process all records regardless of what is selected. Have a second button that says something like 'Process Selected', which processes the selected records. You can fetch all records in the IG, but this could be very inefficient if you have lots of records. if you really want to fetch all records, this forum answer may help https://forums.oracle.com/ords/apexds/post/interactive-grid-select-all-selects-just-40-records-5949

Leon Match3y ago

Hello Jon, Great post indeed. Very useful. One question though. Is it possible to process a row or rows when APEX$ROW_SELECTOR check box is unchecked? In other words, unselected? Thank you, Leon

Jon Dixon3y ago

Hi Leon, thank you for your feedback. Wouldn't it be easier (and more efficient) to pass the selected records to a PL/SQL API, then just process all the records NOT IN the selected list?

You can loop through all records in the IG using model.forEach and then see which ones are checked: https://docs.oracle.com/en/database/oracle/apex/22.2/aexjs/model.html#forEach

Because you want all un-selected records, you will need to make sure you fetch all records from the server into the IG model. You can use this API to do this. Note: Depending on how many records you have, this may be very inefficient. https://docs.oracle.com/en/database/oracle/apex/22.2/aexjs/model.html#fetchAll

Tiago Luis Steil3y ago

Hi,

Thanks for the post, very good!

Do you have some solution where i can check all de records on the grid?

Jon Dixon3y ago

Hi, I usually add a button called 'Process All' or something like that and then have that button call code to process all the eligible records. To select all in the Grid is inefficient and complex as you have to use JS.

Tiago Luis Steil3y ago

Jon Dixon

Hey,

Thanks a lot, this is the same solution I'm adopting

mohankrishna kothapalli3y ago

very good post jon

Jon Dixon3y ago

Thank you, much appreciated.

Jon Dixon3y ago

Thank you, glad you liked it.

Jonathan Kearney3y ago

Another excellent post, all very clearly explained as usual

Jon Dixon3y ago

Thank you Jonathan, much appreciated.

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.

New in APEX 24.1

Since I wrote this post, a new native feature was introduced in APEX 24.1, allowing you to accomplish the same thing I described below. It enables you to define a selectionStateItem in which APEX will populate the primary keys of the rows you select in an Interactive Grid.

Just create a page item and then add the following code to the Initialization JavaScript Function property of your Interactive Grid.

function( config ) {
  config.defaultGridViewOptions = {
    selectionStateItem: "P2_SELECTED_IDS",
    multiple: true
  };
  return config;
}

As you select rows, a ‘:’ delimited list of IDs will be populated automatically:

Oracle APEX IG selectionStateItem

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:

https://youtu.be/TVtdn9klWuw

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

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
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
A Refresh step refreshes the IG to remove the processed records from the IG
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.

https://gist.github.com/jon-dixon/faa1325a8b41b35f939e830291416955

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.

APEX IG JavaScript Utility Source Uploaded to APEX Static Application Files

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.

APEX IG Region Source Attributes

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

APEX IG Region Advanced Static ID

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.

APEX IG Region Attribute Configuration

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.

APEX IG Identify Primary Key Column

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.

APEX IG Row Selector and Row Action

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:

APEX IG Process Selected Dynamic Action Structure

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.

APEX IG Execute Server-side code

💡 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).

APEX IG Process Selected Using SQL

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.

APEX IG Refresh Region after processing

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.

APEX IG Show Success Message

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)

APEX Interactive Grid - Processing Selected Records

Comments (10)

More from this blog

Inside an APEX 26.1 AI Interactive Report Request

Using an APEX AI Agent to Turn Purchase Orders into JSON

APEX AI Agent Handlers: Intercepting & Updating Requests and Responses

APEX AI Agent Logging with Request & Response Handlers

AI Agents Need Boundaries, Not Bigger Prompts

Introduction

New in APEX 24.1

Demonstration

JavaScript Utility Function

Interactive Grid Configuration

Region Configurations

Region Attribute Configurations

Column Configurations

Dynamic Actions

DA Step 1 - Collected Selected IDs

DA Step 2 - Process Selected IDs

DA Step 3 - Refresh IG

DA Step 4 - Complete Message

Preserving Selections Across Pages

Security

Fetching all Grid Records

Conclusion

🔗 Read More

Command Palette

Comments (10)

More from this blog

Introduction

New in APEX 24.1

Demonstration

JavaScript Utility Function

Interactive Grid Configuration

Region Configurations

Region Attribute Configurations

Column Configurations

Dynamic Actions

DA Step 1 - Collected Selected IDs

DA Step 2 - Process Selected IDs

DA Step 3 - Refresh IG

DA Step 4 - Complete Message

Preserving Selections Across Pages

Security

Fetching all Grid Records

Conclusion

🔗 Read More