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.




8 comments:

Jon Trøstheim said...

Hi Morten

Great!, this is something many will find useful, i have had this need many times, great that you made this.

Jon

Unknown said...

I'm looking for help with an ODP.Net + EF5 issue regarding primary key violations. they don't make sense at all considering its all done on Oracle's side via insertion triggers and sequences. Can we get in touch for a few minutes? I have a week left to solve this problem.

Morten Braten said...

@Unknown: And what, exactly, has your question regarding ODP.NET and Entity Framework to do with the subject of this blog post, a PL/SQL plugin for Apex... ? Please stay on topic.

- Morten

Patrick Wolf said...

Hi Morten,

was there a reason not to use "Set Value" action with "Set Type" = "PL/SQL Function Body"? Wouldn't that do the same (except of being able to specify the replace mode and the refresh interval)?

Regards
Patrick

Morten Braten said...

@Patrick: Good question... :-) I guess I assumed that the "Set Value" action would only work for Apex page items, but now I see that the Affected Element can also be set to (for example) any DIV using a jQuery selector. That's certainly useful to know!

However, there is still a difference in that the "Set Value" action with "PL/SQL Function Body" expects you to return everything as a single string (probably with a 32k limit?), whereas the plugin lets you construct HTML of any length and complexity with one or more htp.p calls. Depending on the usage scenario, that may be a significant difference.

Perhaps you could add an option to the "Set Type" dropdown that acts as the "Output of PL/SQL Code" option which is available in the "Based On" attribute for Display Only items? (Or some other way to incorporate the plugin's functionality in the core Apex engine.)

- Morten

Anonymous said...

Hello,

I have implemented your plugin and it is working great. Thanks!

I do have a problem with implementing jquery tabs with it. I have the plsql that puts the div, ul, and li in properly but the tab ui has a jquery function that normal gets called after page load. This binds all of the classes and stuff to the ul and li to act as a tab. Here is the function:

$(function() {
var tabs = $( "#tabs" ).tabs();
tabs.find( ".ui-tabs-nav" ).sortable({
axis: "x",
stop: function() {
tabs.tabs( "refresh" );
}
});
});


Do you know how I can fire this function once your plugin appends the plsql to the div container?

I tried outting it in the plsql, the region, creating a dynamic action, etc. and can not get it to work.

Morten Braten said...

@Anonymous: I actually added a JS callback feature to the plugin in the latest version, which allows you to define a JS function that you would like to be called after the Ajax call finishes (ie after your server-generated content is inserted).

I haven't uploaded this version yet, I'll see if I can do that sometime soon...

- Morten

Anonymous said...

Morten...

Thank you for this plug-in. I have been using it and loving it.

> I'll see if I can do that sometime soon

I have run into a similar situation as described by "Anonymous" before where I want to run some jQuery on the affected DIV after your plug-in is finished. I could really use the "JS callback" functionality you described. I went to the download page and saw that the latest version of the plug-in is the original version from Nov. 2012. Do you have a version with the "JS callback" in it available to the public yet? Thank you.

Shane Bentz.