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

PL/SQL Code

Items to Submit

Items to Return

Success Message

Error Message

Loader

Loader Position

Load CLOB From

Return CLOB Into

Suppress Change Event

Yes

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.

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 Code	`return;`
Success Message	Success!
Error Message	Error!

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 Code	`apex_application.g_x01 := 'New Success Message';`
Success Message	Success!
Error Message	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 Code	`raise NO_DATA_FOUND;`
Success Message	Success!
Error Message	Error!

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

PL/SQL Code	`raise NO_DATA_FOUND;`
Success Message	Success!
Error Message	Error: #SQLERRM#

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

PL/SQL Code	`begin raise NO_DATA_FOUND; exception when NO_DATA_FOUND then apex_application.g_x02 := 'No Data Found :('; raise NO_DATA_FOUND; end;`
Success Message	Success!
Error Message	Error!

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.

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

Characters

P10_LOAD_CLOB_FROM

PL/SQL Code	`apex_application.g_clob_01 := 'ABC' \|\| apex_application.g_clob_01 \|\| 'XYZ';`
Submit CLOB	From Page Item
Submit From	P10_SUBMIT_CLOB_FROM
Return CLOB	Into Page Item
Return Into	P10_SUBMIT_CLOB_INTO

P10_LOAD_CLOB_INTO

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 Code	`apex_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 CLOB	Into JavaScript Variable as JSON
Return Into	myApp.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.

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 Code	`declare 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 Return	P10_ANALYZE_JSON
Submit CLOB	From JavaScript Variable
Submit From	location

P10_ANALYZE_JSON

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.

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 Code	`return;`
Success Message	Success
Loader	Spinner
Loader Position	body

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 Code	`apex_util.pause(3);`
Success Message	Success
Loader	Spinner
Loader Position	body

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 Code	`apex_util.pause(3);`
Success Message	Success
Loader	Spinner & Overlay
Loader Position	body

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

PL/SQL Code	`apex_util.pause(3);`
Success Message	Success
Loader	Spinner
Loader Position	#region_with_spinner

Combination of Spinner & Overlay and a precise selector.

PL/SQL Code	`apex_util.pause(3);`
Success Message	Success
Loader	Spinner & Overlay
Loader Position	#region_with_spinner_overlay

About