Solving the APEX PL/SQL Dynamic Content Region Partial Page Refresh Problem

Photo by Jackson So on Unsplash

Solving the APEX PL/SQL Dynamic Content Region Partial Page Refresh Problem

Jon Dixon's photo
Jon Dixon
·

5 min read

Table of contents

Introduction

Every so often, perfectly valid requirements lead you down a path in APEX that you wish you didn't have to follow. I was recently presented with such a requirement, which forced me to create a 'PL/SQL Dynamic Content' region so that I could dynamically build the HTML content for the region myself. This makes me uneasy for two reasons:

  1. Using the htp.p procedure directly outputs content to the page without escaping it. This means you have to be very careful of the content you output and use apex_escape.html to escape anything that a user can update. Otherwise you run the risk of encountering cross site scripting issues.
  2. The code to build the dynamic content is never as easy to maintain as when using standard APEX components.

Basic Requirement

  • User reviews HTML content generated within the PL/SQL Dynamic Content region
  • User clicks links within the content to Open a Modal and maintain HTML content
  • User closes the modal and expects the content to be refreshed automatically

In this post, I'll describe the problem, a well-known workaround from Scott Spendolini (that didn't work in this case) and the solution that I came up with.

The Problem

Here is an example of refreshing a report region that supports partial page refresh PPR. You have a Classic Report with a link that opens a modal to edit the content of the row. The user closes the modal and wants the report to refresh automatically without submitting the whole page. This is easily achieved by creating a Dynamic Action in the report page (like the below), which fires on 'Dialog Closed'. image.png image.png

When the modal closes, the dynamic action fires and refreshes just the report region and not the whole page. This is thanks to the fact that Classic Reports have the 'Partial Page Refresh' attribute. image.png

Unfortunately, PL/SQL Dynamic Content Regions do not have the 'Partial Page Refresh' attribute. The only (Low Code) way to refresh this region type after closing a modal is to change the dynamic action (Action) to 'Submit Page'. This will then refresh the entire page which is pretty unsatisfactory from a user experience perspective.

One thing I should add at this point is that my dynamic content is made up of several CLOB fields containing HTML content (including images). This content can get much larger than the 32,767 byte limit for a VARCHAR2 field type.

A False Start

A number of years ago, Scott Spendolini came up with an ingenious solution which is outlined in his Blog Post. It essentially involves creating a Classic Report with a SELECT statement which calls a function to build and return the HTML content.

SELECT example_pk.generate_html (p_id => :P10_ID) html_content
FROM   sys.dual;

Now that the content is coming from a Classic Report (which supports PPR) you can refresh it using the 'Refresh' action of a Dynamic Action.

The problem is that this approach only works if you return a VARCHAR type from the function. If you attempt to return a CLOB, you get the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB

This is unfortunate because if you base a Classic Report column directly on a CLOB column from a table then it handles CLOB content just fine.

My Solution

My solution involves three components:

  1. A procedure to generate the dynamic content and add it to an APEX Collection
  2. A Classic Report to query the apex_collections clob001 column
  3. A Dynamic Action which fires on 'Dialog Closed', calls the procedure from step 1 again and then refresh the Classic Report region.

Generate Dynamic Content

I created a Before Header Process in the APEX page, which calls a PL/SQL procedure to Generate the HTML content and add it to the clob001 column in APEX Collections. The logic for the PL/SQL procedure went something like this:

DECLARE
  l_return_html          CLOB;
BEGIN
  -- Code to build up the dynamic content of l_return_html.
  l_return_html := l_return_html || TO_CLOB(<<content1>>);
  l_return_html := l_return_html || TO_CLOB(<<content2>>);
  ..
  -- Create the Collection.
  apex_collection.create_or_truncate_collection(p_collection_name => 'REVIEW_ACTIVITIES');
  -- Add a collection record, populatingn the CLOB column with the dynamic content.
  apex_collection.add_member (p_collection_name => 'REVIEW_ACTIVITIES', p_clob001 => l_return_html);
END;

Classic Report to Show the Content

I created a Classic Report Region based on a SQL statement which selected just the CLOB content from the apex_collections table: image.png

Region Attributes

  • Layout> Number of Rows: 1
  • Template Options > General > Stretch Report
  • Pagination > Type: No Pagination (Show All Rows)
  • Heading > Type: None

Column Attributes

  • Identification > Type: Rich Text
  • Settings > Format: HTML image.png

Dynamic Action to Re-Build the Content and Refresh

I created a Dynamic Action with and Event of 'Dialog Closed'. image.png The dynamic action then fired two Actions. The first called the PL/SQL procedure I mentioned above to re-build the dynamic content and update the CLOB column in the APEX Collection: image.png Then a second Action refreshes the Classic Report Region: image.png

Conclusion

Ideally, PL/SQL Dynamic Content regions should allow Partial Page Refresh and none of the above would be necessary. Unfortunately, 7 years on from Scott's solution, we are still left with workarounds. Hopefully this approach offers a workaround for CLOB content that will get you by until it does become part of the product.

There is hope. It looks like there is an APEX idea for this that is planned for APEX 22.2

Oracle