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.

Monday, November 09, 2015

APEX 5 Cheat Sheet

On Twitter today, Jeff Smith tweeted about a new SQL Developer cheat sheet that he created with a site called cheatography.com.
Not to be outdone, I created a cheat sheet for the APEX 5 Keyboard Shortcuts. Not only can you view it online, but you can also download a PDF version of it. Check it out and let me know if there's anything that you'd like to see added.

Tuesday, November 03, 2015

Hide and Seek

In migrating SERT from 4.2 to 5.0, there's a number of challenges that I'm facing. This has to do with the fact that I am also migrating a custom theme to the Universal Theme, as almost 100% of the application just worked if I chose to leave it alone. I didn't. More on that journey in a longer post later.

In any case, some of the IR filters that I have on by default can get a bit... ugly. Even in the Universal Theme:

2015 11 03 15 25 18

In APEX 4.2, you could click on the little arrow, and it would collapse the region entirely, leaving only a small trace that there's a filter. That's no longer the case:

2015 11 03 15 25 31

So what to do... Enter CSS & the Universal Theme.

Simply edit the page and add the following to the Inline CSS region (or add the CSS to the Theme Roller if you want this change to impact all IRs):

.a-IRR-reportSummary-item { display: none; }

This will cause most of the region to simply not display at all - until you click on the small triangle icon, which will expand the entire set of filters for the IR. Clicking it again makes it go away. Problem solved with literally three words (and some punctuation).