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.

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."

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.

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.

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:

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

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:

begin
    -- 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');
exception
    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
end;

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