Formatted Text with Dynamic Actions

This is a quick note on how to use Dynamic Actions in Oracle APEX 4.0 to display unstructured and formatted text from the database based on user selection. This was a bit more cumbersome in the previous versions of APEX.

Image by Jesper Rønn-Jensen under Creative Commons License.

If you have ever let the users get at the Rich Text Editor items (HTML Editor in the previous versions), then you probably have som text with HTML-markup stored in the database. Below is a description on how to get and display the text based on browser events. The example below will display a description of a department when the user clicks department name in a SQL Report (quite stupid really, but serves the purpose).

Demo Application
I have updated my demo application with the example, see it in action here:http://apex.oracle.com/pls/apex/f?p=45420:5. Download the application here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD.

Prepare the Report
Create a region of type SQL Report based on table DEPT:

select deptno, dname from dept

Modify the DNAME-column in the report:

• Set Link Text to #DNAME#
• Set Link Attributes to id="departmentName#DEPTNO#" (this will uniquely identify each anchor element, and give the ability of extracting DEPTNO from the department name)
• Set Target to URL
• Set URL to # (this really does nothing)

Add an item to the report region, this will eventually hold the DEPTNO of the chosen department from the report.

• Right click the department report region and choose Create Page Item
• Select Hidden
• Give the item a name (P5_DEPTNO)
• Leave the rest default (you might want to reconsider Source Used, if you set it to "Always, replacing any existing value in session state", and leaves Source Value blank, you ensure the item starts with no value when the page is displayed).

Create Region to Display Text
For the sake of simplicity, the text will be displayed in it's own HTML Region

Right click Regions and choose Create

• Choose HTML
• Choose HTML again
• Give it a descriptive title: Department Description
• Create a div to display the text, set Region Source to:
• <div id="departmentDescription"></div>

Create the Dynamic Action
The first we need to do, is to create a Dynamic Action to get the DEPTNO from the chosen department name, and stuff it into P5_DEPTNO.

Right click Dynamic Actions and choose Create
Choose Advanced
Give it a name (onclick - Department Name)
Set the values as seen below, this means the Dynamic Action will fire when anchors with id's starting with departmentName is clicked

Set Action to Set Value, uncheck Fire on Page Load (we only want it to fire when clicked), set the Set Type to Javascript, and Javascript Expression to

this.triggeringElement.id.replace('departmentName','');

This will extract the DEPTNO from the id of the element clicked (by replacing departmentName with nothing).

Set Selection Type to Item(s), and transfer P5_DEPTNO to the right hand side

The final result should look something like this:

The next step is to get and display the desired text from the database. In order to do this we must create a new True Action to the same Dynamic Action.

• Right click the Dynamic Action and choose Edit
• Click Add True Action
• Set Action: SetValue
• Uncheck Fire on Page Load
• Set Set Type: PL/SQL Function Body
• Set PL/SQL Function Body to code below:

declare
   l_ret varchar2(32000);
begin
   for r in (select dep.*
               from dept dep
              where dep.deptno = :p5_deptno)
   loop
      l_ret := 'HTML formatted return value for <b>'|| r.dname ||'</b> located in <i>'|| upper(r.loc) ||'</i>.';
   end loop;
   return l_ret;
end;

• Set Page Items to Submit: P5_DEPTNO
• Set Escape Special Characters: No
• Set Selection Type: jQuery Selector
• Set jQuery Selector: div[id=departmentDescription]

Finally edit the Dynamic Action, and set Event Scope: live. This is to ensure the onclick event is attached to Department Name even after PPR refresh of the report.

A Word of Caution
Even though APEX takes care of escaping/encoding your HTML according to JSON specification, this will bloat your return message. There seems to be the standard PL/SQL 32k limit to the JSON-message (Large texts resulting in ORA-06502).

Take care what you return in your message, switching off escape special characters leaves it pretty much wide open to anything. HTML in JSON is a debated issue.

In short: Keep it small and neat :-)