Using the plug-in PL/SQL API only

When application development involves procedures and functions stored in database packages rather than adhering strictly to low-code principles, the plug-in PL/SQL API emerges as an enticing avenue for handling CLOB data with the Rich Text Editor Pro. This API exposes a range of procedures and functions designed to facilitate interaction with the plug-in session state, especially in cases where Oracle APEX session state lacks native support for CLOB values within page items.

The forthcoming instructions delineate a step-by-step approach for creating a report that features rich text documents linked with a modal page powered by the Rich Text Editor Pro.

Prerequisites

Before implementing the plug-in please read the implementation prerequisites described in parent document.

Create report page with modal page

In the application builder home page click Create Page button.

In the Create Page wizard step select Form and click the Next button.

In the Create Page wizard step select Report with Form and click the Next button.

In the Create Report with Form (Page Attributes) wizard step:

1. Set Report Type to Interactive Report
2. Set Report Page Number to 4
3. Set Report Page Name to PL/SQL API only - report
4. Set Form Page Number to 5
5. Set Form Page Name to PL/SQL API only - modal
6. Set Form Page Mode to Modal Dialog
7. Click Next button

In the Create Report with Form (Navigation Menu) wizard step:

1. Set Navigation Preference to Create a new navigation menu entry
2. Set New Navigation Menu Entry to PL/SQL API only
3. Click Next button

In the Data Source (Data Source) wizard step:

1. Set Data Source to Local Database
2. Set Source Type to Table
3. Set Table / View Name to UC_FROALA_SAMPLE_CLOBS
4. In Select Columns to be shown in the report select ID, DESCRIPTION and CLOB_CONTENT

In the Create Form - Columns and Primary Key (Form Page) wizard step:

1. In Select Columns to be shown in the form select ID, DESCRIPTION and CLOB_CONTENT
2. Set Primary Key Column to ID (Number)
3. Click Create button

New pages are created with all required components. Navigate to page 4 (PL/SQL API only - report and hide column CLOB_CONTENT so it won't raise interactive report error when CLOB content exceeds VARCHAR2 limitation.

Follow these steps:

1. Select Report 1
2. Expand Columns
3. Select column CLOB_CONTENT and set Identification \ Type to Hidden Column

Remove form components

about this step

Removed processes in this step are native form component processes loading, creating, updating and deleting rows in the database table. These components were created by APEX wizard and they will be replaced with custom PL/SQL processes implementing the plug-in PL/SQL API.

Navigate to page 5 PL/SQL API only - modal and remove form components.

1. Select before header and processing processes and using DELETE key remove these processes.
2. Select region PL/SQL API only - modal and change Type to Static Content

Before header

Processing

Form region

Enable page item plug-in

about this step

Enabling plug-in uploading images is optional, not uploaded images remain in a document as is without modyfing document HTML.

Navigate to page 5 PL/SQL API only - modal and update page item P5_CLOB_CONTENT by following these steps:

1. Set Identification \ Type to UC - Rich Text Editor Pro [Plug-in].
2. Set Appearance \ Template to Optional \ Above
3. Set Settings \ Upload Image(s) on Request(s) to SAVE,CREATE

Page processes

Once, the native Form component is removed from the APEX page, the CLOB operations (create, update and delete) must be re-implemented using the plug-in PL/SQL API.

Before header - load a document

Create a new Pre-Rendering \ Before Header process named Load CLOB from the database and

1. Set Identification \ Type to PL/SQL Code
2. Set Source \ PL/SQL Code
3. Set Success Message to Rich text document (&P5_ID.) loaded using PL/SQL API.
4. Set Server-side Condition \ Type to Item is NOT NULL
5. Set Server-side Condition \ Item to P5_ID

Page designer

Process PL/SQL Code

declare
  v_clob_row      UC_FROALA_SAMPLE_CLOBS%ROWTYPE;
begin
  select * into v_clob_row from UC_FROALA_SAMPLE_CLOBS where id = :P1435_CLOB_ID;
  
  UC_FROALA_RTE.setValue(   
    p_item_name => 'P5_CLOB_CONTENT',
    p_value     => v_clob_row.CLOB_CONTENT
  );
end;

Processing - create a document

Create a new Processing \ Processes process named Create CLOB in the database and

1. Set Identification \ Type to PL/SQL Code
2. Set Source \ PL/SQL Code
3. Set Success Message to A new rich text document (&P5_ID.) created using PL/SQL API.
4. Set Server-side Condition \ When Button Pressed to CREATE

Page designer

Process PL/SQL Code

declare
  v_clob        clob;
  v_new_id      number;
begin
  v_clob:= UC_FROALA_RTE.getValue(   
    p_item_name => 'P5_CLOB_CONTENT'
  );
  
  insert into UC_FROALA_SAMPLE_CLOBS(
    SESSION_ID,
    CLOB_CONTENT,
    DESCRIPTION
  ) values(
    :APP_SESSION,
    v_clob,
    :P5_DESCRIPTION
  ) returning id into v_new_id;
  
  apex_util.set_session_state('P5_ID', v_new_id);

end;

Processing - update a document

Create a new Processing \ Processes process named Update CLOB in the database and

1. Set Identification \ Type to PL/SQL Code
2. Set Source \ PL/SQL Code
3. Set Success Message to Rich text document (&P5_ID.) updated using PL/SQL API.
4. Set Server-side Condition \ When Button Pressed to SAVE

Page designer

Process PL/SQL Code

declare
  v_clob clob;
begin
  v_clob:= UC_FROALA_RTE.getValue(   
    p_item_name => 'P5_CLOB_CONTENT'
  );
  
  update UC_FROALA_SAMPLE_CLOBS set
    CLOB_CONTENT = v_clob,
    DESCRIPTION  = :P5_DESCRIPTION
  where
    id = :P5_ID
  ;

end;

Processing - delete a document

Create a new Processing \ Processes process named Delete CLOB in the database and

1. Set Identification \ Type to PL/SQL Code
2. Set Source \ PL/SQL Code
3. Set Success Message to Rich text document (&P5_ID.) removed.
4. Set Server-side Condition \ When Button Pressed to DELETE

Page designer

Process PL/SQL Code

begin
  delete from UC_FROALA_SAMPLE_CLOBS where id = :P5_ID;
end;

Update Close dialog execution sequence so it's the last process to be executed.

Test it

This simple and easy to implement instruction showcase how easy is utilizing the plug-in PL/SQL API to load, create and update rich text documents using the plug-in in.

To test the implementation using interactive report and modal page implementing the plug-in run page 4 - Rich Text documents (PL/SQL API).

Interactive report preview

Modal preview

Test the implementation on-line

The plug-in sample application showcase this implementation in the example page Handle CLOB using the plug-in PL/SQL API.