Tuesday, November 6, 2012

Apex Plugin: Execute PL/SQL code and return content to page

Here is a Dynamic Action plugin for Oracle Application Express (Apex) that lets you execute PL/SQL code in the database, and then send output generated on the server (via calls to the "HTP.P" procedure) back to any element on the web page.

A more specific use case would be to respond to the clicking of a button by generating a fragment of HTML using PL/SQL, and then refresh a DIV on the page with that dynamically generated content. See the screenshot below for an example; notice that part of the first region has been updated with content generated on the server (using input from the user, in this case the name from a regular text input item). Every time the user clicks on the button, the line above the button is updated using an Ajax call to the server.

The second region illustrates another use case: Automatically refreshing part of the page at a fixed interval.

This is how the page has been set up:

The first region has a standard text input item, and a button. The button has a Dynamic Action that is triggered when the button is clicked. The click executes the PL/SQL code which generates the greeting.

The dynamic action is set up as follows:

We can see that the content generated by the PL/SQL code is returned into a page element called "my_div". This div is simply included as the static content of the HTML region:

The second dynamic action has been set up to trigger on "Page Load". The plugin is set up to call the PL/SQL code every 5 seconds (nice way to hammer your database with requests...) and return the content into another div, called "my_other_div". The mode has been set to "Append after", which means the content will be added to the end of the existing content, instead of replacing it.

Here is the setup for the second region:

Conclusion: This plugin combines the flexibility of Dynamic Actions with the power of PL/SQL for dynamic content generation. You can download the plugin here. Update 22.05.2016: This plugin is now in my plugin repository at GitHub.