Skip to Main Content

Breadcrumb

Overview

The FOS - Execute PL/SQL Code dynamic action plug-in introduces some enhancements to the existing "Execute PL/SQL Code" dynamic action that APEX provides. These additional features include:

  1. Providing a processing icon whilst optionally masking the background either at the region or page level
  2. Declarative Success & Error Notifications
  3. Submitting and returning CLOB data using page items or Javascript variables
  4. Returning the execution results to a Javascript variable
  5. Optionally suppressing the change event on page item values that are returned

Examples

Processing Icon

You have the option to show a spinner/processing icon with an optional modal overlay to block user interaction whilst the PL/SQL code is being executed e.g. for code that takes a second or longer to run. The following example shows this behaviour being applied to the whole page i.e. the default behaviour


Showing the Processing Icon/Mask on a Region instead of the Page

In some cases you may not want to show the processing icon (optionally with the mask) in the center of the page but rather a particular region the execution of PL/SQL code belongs to. This allows the user to interact with the page when you apply the mask overlay setting, except for interacting with the region being masked. In this case we can use the "Affected Element" of the dynamic action to control what element the icon/mask is applied to e.g.

Notifications

In order to reduce the number of dynamic actions we create, rather than relying on an external notification plug-in or relying on Javascript code, we are providing declarative support for success/error notifications. You have the option of using the APEX message API or using FOS notifications (which is the default).


Override the Success Message

In some cases you may want to override the success message being returned. You can easily do this by updating the following variable in your code e.g.


apex_application.g_x01 := 'My New Success Message!!';

Override the Success Message including a Title

In some cases you may want to override the success message being returned. You can easily do this by updating the following variable in your code e.g.


apex_application.g_x01 := 'My New Success Message!!';
apex_application.g_x02 := 'Success';

Override the Notification Type

In some cases you may want to override the success message being returned and also change the notification type. You can easily do this by updating the following variable in your code e.g.


apex_application.g_x01 := 'My new task completion message!!'; -- message
apex_application.g_x02 := 'Task Complete';                    -- (optional) title
apex_application.g_x03 := 'info';                             -- type [success|warning|info|error]

Or if you wanted to handle our own exceptions you could do 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!!'; -- message
        apex_application.g_x02 := 'Error'; -- title
        apex_application.g_x03 := 'error'; -- type
        rollback;
end;

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:


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;

Commit/Rollback/Raise

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.


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


Setting Popup LOV Display Values

Popup LOV Display Values can be set easily by using the DA's "Page Items to Return" where only the return value needs to be passed and the plug-in figures out the display value automatically.

CLOB Data

One of the pain points with in APEX is the lack of declarative support for transferring/receiving data > 32K. To alleviate this issue we have added CLOB support to this dynamic action. You can transfer a page item with a CLOB value to the server and back to the browser by performing the following button actions:


Getting & Returning your CLOB directly into a Javascript Function

From v21.1 onwards you can now (optionally) get your CLOB value directly from a Javascript Function and (optionally) return the JSON result directly into a Javascript Function defined in the "Initialization Javascript Code". We are expecting two functions to be named "submitClob" and "clobCallback" e.g.


function(options) {
    var itemName = "P1020_MESSAGE";
    options.submitClob = function() {
        return JSON.stringify({
            message: $v(itemName)
        });
    };
    options.clobCallback = function(data) {
        apex.message.showPageSuccess(data.message);
    };
    return options;
}
62 of 4000

Design

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.

FAQ

  • Is there anything the regular APEX "Execute PL/SQL Code" action does that this plug-in can't do?

    The short answer is "No". We have included this plugin to perform additional capabilities to avoid the use of any extra actions or Javascript code. As developers ourselves we have found it quite common to use notifications after executing some PLSQL as well as showing some visual indication that something is being processed on the server. In order to reduce the number of dynamic actions that developers create we provided the advanced instructions to keep them to a minimum which should help with maintenance and performance, whilst giving developers more serverside control of what happens on the screen.