About

APEX Utils - Execute PL/SQL Code


This plugin is meant to be a replacement for the native Execute PL/SQL Code dynamic action. It compensates for many features the native component lacks, while only featuring the most common functionality most developers might need.

Features include:

  • Declarative & overridable Success & Error messages
  • Submitting & returning a CLOB, with JSON in mind
  • Configurable spinners for longer processes

Settings Preview

Items to Submit
Items to Return
Load CLOB From
Return CLOB Into
Suppress Change Event

Notifications

Info

Unlike the native Execute PL/SQL Code DA, the APEX Utils equivalent gives you control over success and error messages.

This plugin offers an attribute for an optional success message and therefore eliminates the need for a second dynamic action. The message can also be dynamically generated in the PL/SQL block.

There is also an optional error message attribute. The error will be displayed via apex.message.showErrors as opposed to apex.message.alert for more consistency with the rest of APEX, but that can be changed as well. This attribute can also be overridden in PL/SQL.

Normal Success

The most common use is to display a simple success message if the PL/SQL code completed correctly.

Leaving out the message will result in no notification being displayed.

PL/SQL Codereturn;
Success MessageSuccess!
Error MessageError!

Overridden Success

To have more control over the notification, you can use the apex_application.g_x01 variable, which will override the declared success message.

PL/SQL Codeapex_application.g_x01 := 'New Success Message';
Success MessageSuccess!
Error MessageError!

Normal Error

In case of an error, the Error Message attribute will be used.

Leaving out the message will result in no notification being displayed.

PL/SQL Coderaise NO_DATA_FOUND;
Success MessageSuccess!
Error MessageError!

Substitution Strings

For more detailed information, you can use the #SQLCODE#, #SQLERRM# or #SQLERRM_TEXT# substitution strings.

PL/SQL Coderaise NO_DATA_FOUND;
Success MessageSuccess!
Error MessageError: #SQLERRM#

Overridden Error

If populated, the apex_application.g_x02 variable will override the declared error message.

PL/SQL Codebegin raise NO_DATA_FOUND; exception when NO_DATA_FOUND then apex_application.g_x02 := 'No Data Found :('; raise NO_DATA_FOUND; end;
Success MessageSuccess!
Error MessageError!

CLOB Support

Info

Declarative CLOB support for Execute PL/SQL Code is finally here!

The typical usecase is submitting and retrieving values larger than 4000 characters in size. You can specify a Page Item whose value will be submitted to the server, and made available to you as apex_application.g_clob_01. Same goes for retrieving a CLOB. Provide a Page Item, and the CLOB under apex_application.g_clob_01 at the end of the execution will be loaded into it.

What's more, if that data is not meant to be visible, there is no need to use an extra hidden Page Item. You can simply load data from and into global JavaScript variables. Furthermore, one can use the APEX_JSON API to create or consume a JSON Object in the backend, and transport it easily with this plugin.

Example 1 - Submitting and returning a CLOB from and into a Page Item

In this example, we generate a CLOB in the front end, submit it to the server, alter it, and send it right back.

PL/SQL Codeapex_application.g_clob_01 := 'ABC' || apex_application.g_clob_01 || 'XYZ';
Submit CLOBFrom Page Item
Submit FromP10_SUBMIT_CLOB_FROM
Return CLOBInto Page Item
Return IntoP10_SUBMIT_CLOB_INTO

Example 2 - Creating and returning a JSON object

In this example, we create a JSON Object in the backend, transport it to the front end via the apex_application.g_clob_01, and load it into the myApp.data JavaScript variable.

PL/SQL Codeapex_json.initialize_clob_output; apex_json.open_object; apex_json.write('appName', 'APEX Utils'); apex_json.write('pluginName', 'Execute PL/SQL Code'); apex_json.open_array('numbers'); apex_json.write(1); apex_json.write(2); apex_json.write(3); apex_json.close_array; apex_json.close_object; apex_application.g_clob_01 := apex_json.get_clob_output; apex_json.free_output;
Return CLOBInto JavaScript Variable as JSON
Return IntomyApp.data

You can now reference myApp.data or window.myApp.data from any JavaScript context.

Open up your console and check that it was actually created.

Example 3 - Submitting and consuming a JSON Object

We can also move the JSON in the other direction. Let's take for example window.location which is a global object that holds many important bits of information. We can submit it to the server, parse it (as it was stringified in order to travel through,) and analyze it with the help of APEX_JSON.

PL/SQL Codedeclare l_values apex_json.t_values; l_children_count number; l_href varchar2(1000); begin apex_json.parse( p_values => l_values , p_source => apex_application.g_clob_01 ); l_children_count := apex_json.get_count( p_path => '.' , p_values => l_values ); l_href := apex_json.get_varchar2( p_path => 'href' , p_values => l_values ); :P10_ANALYZE_JSON := 'JSON member count: ' || l_children_count || '
' || 'Href attribute: ' || l_href; end;
Items to ReturnP10_ANALYZE_JSON
Submit CLOBFrom JavaScript Variable
Submit Fromlocation

Spinner

Info

This plugin also brings a proper loading indicator for longer processes.

It offers the attribute "Loader" with choices "Spinner", "Spinner & Overlay" and "None."

You can also provide the spinner's position as a jQuery selector.

Short Process

If you specify a spinner but the AJAX call finishes very quickly (<200ms), no spinner will be shown to avoid flickering. This makes use of the native apex.util.delayLinger API.

PL/SQL Codereturn;
Success MessageSuccess
LoaderSpinner
Loader Positionbody

Long Process

For longer processes it makes sense to show a spinner to let the user know that something is going on.

Note that if the AJAX call takes longer than 200ms, the spinner will always show for at least 800ms to avoid flickering.

PL/SQL Codeapex_util.pause(3);
Success MessageSuccess
LoaderSpinner
Loader Positionbody

Spinner & Overlay

If you wish to discourage the user from performing other actions while the AJAX call is in progress, you may want to place an overlay on the screen. Note that this will not stop actions performed via the keyboard.

PL/SQL Codeapex_util.pause(3);
Success MessageSuccess
LoaderSpinner & Overlay
Loader Positionbody

Spinner On Region

A more precise position for the spinner can also be provided as a jQuery selector.

PL/SQL Codeapex_util.pause(3);
Success MessageSuccess
LoaderSpinner
Loader Position#region_with_spinner

Spinner & Overlay On Region

Combination of Spinner & Overlay and a precise selector.

PL/SQL Codeapex_util.pause(3);
Success MessageSuccess
LoaderSpinner & Overlay
Loader Position#region_with_spinner_overlay