Tuesday, November 10, 2015

Refreshing PL/SQL Regions in APEX

If you've been using APEX long enough, you've probably used a PL/SQL Region to render some sort of HTML that the APEX built-in components simply can't handle. Perhaps a complex chart or region that has a lot of custom content and/or layout. While best practices may be to use an APEX component, or if not, build a plugin, we all know that sometimes reality doesn't give us that kind of time or flexibility.

While the PL/SQL Region is quite powerful, it still lacks a key feature: the ability to be refreshed by a Dynamic Action. This is true even in APEX 5. Fortunately, there's a simple workaround that only requires a small change to your code: change your procedure to a function and call it from a Classic Report region.

In changing your procedure to a function, you'll likely only need to make one type of change: converting and htp.prn calls to instead populate and return a variable at the end of the function. Most, if not all of the rest of the code can remain untouched.

Here's a very simple example:

Before:

PROCEDURE print_region
  (p_item IN VARCHAR2)
IS
BEGIN
htp.prn('This is the value: ' || p_item);
END;

After:

FUNCTION print_region
  (p_item IN VARCHAR2)
RETURN VARCHAR2
IS
  l_html VARCHAR2(100);
BEGIN
l_html := 'This is the value: ' || p_item;
RETURN l_html;
END;

On the APEX side, simply create a Classic Report and set the query to something like this that refers to your function:

SELECT package_name.function_name(p_item => :P1_ITEM) result FROM dual
You'll then want to edit the Attributes of the Classic Report and turn off Pagination, set the Headings type to None and ensure Partial Page Refresh is enabled. Next, click on the Template Options and Disable Alternating Rows and Row Highlighting and then check Stretch Report.

2015 11 10 08 56 05

Make any other UI tweaks that you need, and you should now have a Dynamic PL/SQL Region that can be refreshed in a Dynamic Action.

5 comments:

Anonymous said...

I created AM42 in the APEX feature request app: https://apex.oracle.com/pls/apex/f?p=55447 on reading this. Please vote for it!

Morten Braten said...

Or you can just use my "Execute PL/SQL Code and Return Content" plugin that I created a few years ago... :-) http://ora-00001.blogspot.com/2012/11/apex-plugin-execute-plsql-code-and-return-content-to-page.html Latest version available at https://github.com/mortenbra/apex-plugins/tree/master/execute-plsql-and-return-content-dynamic-action

Scott said...

Didn't know such a thing existed! Thanks for sharing.

- Scott -

Juergen Schuster said...

Me too, I know a good place to post it :-)

https://apex.world/ords/f?p=100:700

ChrisB said...

Scott, you forgot to mention that you need to edit the "result" column and set "Escape Special Characters? to No.