Skip to Main Content



The FOS - Interactive Grid - Process Rows plug-in is used for bulk-processing rows of an interactive grid. It is a dynamic action that runs PL/SQL code on either only the selected rows or all filtered rows.

You can pass page-items in and out of the PL/SQL code, display success and error messages, programmatically control success and error message content and behaviour, declaratively refresh the selected rows or the whole grid after completion, and it also takes care of message substitutions and escaping.


Process Selected Rows

One feature the Interactive Grid has always lacked is the declarative ability to perform processing on only the selected rows. This plug-in makes such a requirement trivial. Simply set the mode to "Selection" and provide your PL/SQL code. The code will run once for each selected row. Furthermore, you can access column values via bind variables.

In the following example, we managed to increase each of the selected employees' salaries by 100, by providing the following code.

update fos_emp
   set sal   = sal + 100
 where empno = :EMPNO;

To refresh the selection after processing, we toggled on the option "Refresh Selection After Processing."

This plug-in also works great in combination with the "FOS - Interactive Grid - Add Button" plug-in. In this case, we've added the "Give a Raise" button to the toolbar on page load, which in turn triggers the "FOS - Interactive Grid - Process Rows" Dynamic Action, and toggles on the option "Disable On No Selection."

emp - selection

Process Filtered Rows

The following example demonstrates the "Filtered Rows" mode.

If you click "Give a Raise", all records in the query will be updated. However, as you add filters, only the records in the result set will be updated.

emp - filtered

Items to Submit/Return

This plug-in also supports items to submit & return.

In the following example, the mode is set to "Process Filtered Rows" like before, but in addition we submit item P2020_NEW_SAL and return P2020_UPDATE_COUNT

To demonstrate, give P2020_NEW_SAL a numeric value and click "Give a raise." This will execute the following code on the server, for each filtered row.

:P2020_UPDATE_COUNT := to_number(nvl(:P2020_UPDATE_COUNT, '0')) + 1;

update fos_emp
   set sal   = :P2020_NEW_SAL
 where empno = :EMPNO;

During each operation, we will use P2020_UPDATE_COUNT to keep track of how many rows have been updated, and display a success message accordingly.

emp - items

Success and Error Messages

One can also define success and error messages declaratively, avoiding having to create a second or third custom action.

Simple Success Message - An even simpler way to show a success notification is by populating the Success Message attribute. This attribute also supports substitution strings, which can be either substituted on the server after the processing is complete, or on the client if you choose so. In this example we are only showing a hardcoded string.

Overridden Success Message - If a static message, or even a message referencing item values isn't enough, you can have even more control by populating the apex_application.g_x01 variable with a custom success message. You can do so in the PL/SQL code block.

Simple Error Message - Similar to the simple success message, you can define a static error message as well. This is what will be shown in case of an unhandled exception. Besides item values, you can also reference the following substitution strings in this message: #SQLCODE#, #SQLERRM# and #SQLERRM_TEXT#.

Overridden Error Message - For even greater control, you can override this message in the PL/SQL code block itself by populating the apex_application.g_x01 variable. Make sure however to still raise the error, so the changes can be rolled back and the error message logged properly. For example:

    -- your processing code here
    raise_application_error(-20001, 'oh no!');
    when others then
        apex_application.g_x01 := 'Overridden error message';

emp - messages

Additional Control

In addition to overriding the notification using APEX global variables. We also allow you to cancel following actions, and trigger a custom event. These can be useful in situations when you are handling exceptions in your own code.

Cancel Following Actions

In some cases you may want to stop further actions from continuing if you performed your own error handling. This can be achieved by setting the following APEX global variable in your code e.g.

apex_application.g_x04 := 'cancel'; -- stop/cancel following actions

Fire a Custom Event

In some cases when you run into an error you may want to perform some other dynamic action when this occurs. This can be achieved by setting the following APEX global variable in your code e.g.

apex_application.g_x05 := 'plsql-exception-20001'; -- fire custom event on the "body" tag

Putting these two features together will look something like this:

    -- do something here first
    apex_util.pause(1); -- pause for demo purposes only
    -- raising a dummy exception for example purposes
    raise_application_error(-20001, 'Something went wrong');
    when others then
        -- override the FOS Notification
        apex_application.g_x01 := 'We just ran into a big problem!!';
        apex_application.g_x02 := 'Error';
        apex_application.g_x03 := 'error';
        apex_application.g_x04 := 'cancel'; -- stop/cancel following actions
        apex_application.g_x05 := 'plsql-exception-20001'; -- fire custom event on the "body" tag
        rollback; -- recommended since an expcetion has occurred

emp - additional control


When you provide your own exception handler, you have the option of manually rolling back yourself and have control over the behaviour of what happens next, as per the above demo.

If you don't rollback or re-raise the exception, any changes made before the exception will be committed as the APEX engine performs an implicit commit for you in every AJAX call. Alternatively if you do issue a "raise;" statement, any changes will be implicitly rolled back for you, the notification type will be set to "error", and any following actions will be cancelled e.g.

    when others then
        -- override the FOS Notification
        apex_application.g_x01 := 'We just ran into a big problem!!';


You can preview the plug-in setup as you would see it in page designer. You can either do this by clicking this button in the top right corner of each example, or you can see all the examples together in the region below.

Looking at the examples you'll see just how easy the plug-in is to use. Don't worry about changing any values as they aren't saved. We actually encourage you to change them, so you can see the behaviour of the attributes and their help text.

APEX 19.2

Note for APEX 19.2 users

If you use this plug-in in APEX 19.2, there is a small APEX bug in the API this plug-in uses for which we must provide a workaround.

If your grid is editable and therefore has the APEX$ROW_SELECTOR and APEX$ROW_ACTION columns, or it has any other virtual column that is not part of the Interactive Grid query, you must provide the following workaround or else an error will be thrown.

For all of these columns, add a server-side condition of type PL/SQL Expression with value: nvl(apex_application.g_x01, 'x') != 'FOS_APEX_192_BUG_30665079_WORKAROUND'

This will not affect your columns at all, but it will ensure that no error will be thrown when executing the dynamic action.

In APEX 20.1 and above, this bug has been fixed, so this workaround will not be needed anymore but will also not break anything if you leave it in.


  • What happens when a PL/SQL Exception is raised in my code?

    In the case of a PL/SQL unhandled exception being raised, all changes will be rolled back, an error notification is shown, and following actions are cancelled.

    As per "Example 4 & 5", you can show a custom error in your PL/SQL

  • Why process all filtered rows when I can just select all rows, and process these?

    The way the Interactive Grid works, is that not all rows in the result set are sent to the browser, for performance reasons. If you have thousands of records, these will be split into pages, and often only 1 page is sent to the browser. When you click to "Select all rows", in reality only the visible rows are selected, but there could potentially be many more that only the server knows about. In such a case, use "Process Filtered Rows" to overcome this constraint.