Saving Rich Text Content to the DB Via an Ajax Process in APEX

Saving Rich Text Content to the DB Via an Ajax Process in APEX

ยท

4 min read

Introduction

First of all, why am I writing about this? APEX has supported CLOBs in Rich Text Items forever (well, since APEX 22.2, anyway ๐Ÿ˜Š). I am writing this post because I have a particular use case where I must pass a Rich Text Editor (RTE) page item CLOB value to an Ajax process.

I was building a notes App where users can author documents in APEX and perform Oracle Text searches on those documents. After the initial creation of the note, the focus is entirely on one full-screen RTE Note, like the screenshot below.

Screenshot showing Oracle APEX Notes App

Even though I have 2 Save buttons, my users expect their progress to be saved as they work. When typing lengthy documents, they are focused on the content, moving away to get screenshots, etc., and they won't always remember to save.

Requirements

The complete requirements are as follows:

  • Save the RTE content behind the scenes without distracting the user.

  • Do not let changed content go more than 30 seconds before saving.

  • Take a backup copy of the previously saved note before saving changes.

  • Do not attempt to save the note if it has not been updated.

  • Handle lost update protection in case two users work on the same note.

How

As with all Ajax-related work, we need to split the task into two parts. The JavaScript part and the PL/SQL process part.

The JavaScript Part

Note: Some parts of the solution are specific to the TinyMCE Rich Text editor introduced in APEX 23.1. However, the concepts can be applied to any version of APEX and the RTE.

Function and Global Variable Declaration

The code below goes into the 'Function and Global Variable Declaration' page property. I have included detailed comments to help explain what is going on. Essentially, the code defines some variables and a function called saveNote. The saveNote function checks to see if the RTE APEX page item has changed, and if it has, calls an Ajax Process SAVE_NOTE, which contains PL/SQL to save the note to the DB.

A JavaScript IntervalsaveInterval calls the saveNote function every X seconds. The JavaScript Interval is initiated in the section below 'Execute When Page Loads'.

A few key points here:

  • The APEX JavaScript API apex.server.chunk is doing a lot of the heavy lifting by splitting the CLOB into an array with no more than 8,000 chars in each element.

  • You will notice that I am using the TinyMCE API tinymce.activeEditor.isDirty() to check if the RTE field has changed. I cannot use apex.item( "P50_NOTE_CONTENT" ).isChanged(), because the page item is not marked as changed until the user tabs out of the RTE field.

  • I am maintaining a Checksum for just the content of the RTE item in a page item called P50_NOTE_CHECKSUM.

Execute when Page Loads

The code below starts the JavaScript Interval saveInterval, which is calls the saveNote function (defined above) every saveIntervalMS milliseconds.

/* Start AutoSave */
saveInterval = setInterval(saveNote, saveIntervalMS);

The PL/SQL Part

Ajax Callback Process

The first thing we need to do is define an Ajax Callback process on the APEX Page. In this example, I am passing the values set in the JavaScript above into a PL/SQL procedure called save_note_bg.

Oracle APEX Ajax Callback

PL/SQL Logic

The procedure save_note_bg looks like this:

The only other PL/SQL code involved runs in a before Header Page Process. This code gets the initial checksum when the user first visits the page or refreshes the page.

IF apex_session_state.get_clob('P50_NOTE_CONTENT') IS NOT NULL THEN
  l_note_checksum := dbms_crypto.hash(apex_session_state.get_clob('P50_NOTE_CONTENT'),1);
  apex_util.set_session_state('P50_NOTE_CHECKSUM', l_note_checksum);
END IF;
-- Set page items refrenced in the saveNote JS Function.
-- Check for changes every 30 seconds (30000 milliseconds).
apex_util.set_session_state('P50_SAVE_INTERVAL_MS', 30000);
-- Set how long the Interval should keep checking for changes.
--   30 Seconds Per Check * 120 Iterations = 3600 Seconds (1 Hour).
apex_util.set_session_state('P50_SAVE_ITERATIONS', 120);

At a high level, the process looks like this:

Conclusion

Saving page items behind the scenes is one of those things you will not need to do very often, but knowing how to do it is useful. This pattern can also be used when you need to perform other actions behind the scenes without requiring an action on the user's part.

๐Ÿ”— Read More

ย