Wednesday, January 30, 2019

APEX Plugin: Execute PL/SQL code and return content to page (updated)

Oracle APEX has a built-in Dynamic Action called "Execute PL/SQL Code" that executes a block of PL/SQL code on the server via an Ajax call (ie does not do a regular submit and reload of the whole page).

But what if you want to do something on the server AND also return some content back to the client? Back in 2012 I released an APEX Dynamic Action plugin called "Execute PL/SQL Code and Return Content" that allows you to do just that. Read the original blog post for more information.

Since then, the APEX framework has evolved and the old Ajax workhorse function called "htmldb_Get" has been deprecated and replaced with functions in the "apex.server" namespace. Hence I have updated my plugin to use the modern API.

You can find the updated version (1.1) of the plugin on my GitHub page for APEX plugins. Note that the new plugin has been exported from APEX 18.1 and therefore requires at least that version to import and use.

Enjoy! :-)


Juergen Schuster said...

Hi Morten, why don't you publish your Plug-in on along with the other 180 Plug-ins, this is the place people look for Plug-ins, since you are on Github it's very easy for you just check the section Github for Plug-ins / Github for Contributors for a detailed explanation.

Morten Braten said...

@Juergen: The catalog of plugins is great. The reason I haven't published my plugins there is because I am lazy and keep all my plugins in a single GitHub repository. To publish them on I would have to split them into a separate repository for each plugin. Not a big deal but still a hassle.

A suggestion from me to make your catalog more flexible would be to also allow for an "apexplugins.json" (note plural) file in the GitHub repo root, which could then be an array of plugins (with the same structure as apexplugin.json) that could then contain multiple plugins published from a single repo. A small enhancement that could be useful for more than me? :-)

- Morten

Fahd said...

If i understand correctly we don't need plugin to achieve said functionality in apex 5.1.4.

We can Execute PLSQL code in dynamic action to both execute PLSQL on server side as well as return data to client using property "Items to Return" .

Morten Braten said...

@Fahd: It depends. The built-ins expect 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.

- Morten

YuriAP said...

Hi Morten,

I just tried to use the plugin on the newest APES, it somehow does not work. Although, it works on for me.

Do you have any plans to fix the plugin for APEX 20?
Thank you in advance.

Morten Braten said...

@Yuri: I just tested the plugin (v1.1) on, it works as expected. If you can make a test case on to show that it doesn't work, I can take a look.

- Morten