CKEditor and Those Pesky CLOBs

Update (11-APR-2013): Check out Enkitec CLOB Load Plug-in for APEX in the Wild for a plug-in to make this easier.

For a while now APEX has come with a neat little item type… the HTML editor. It is available in two flavors: standard and minimal. Whenever an end user needs the ability to edit some HTML content, whether it’s for a web site, an email, or whatever else, you’re able to utilize one of these two items with minimal effort.

For me, however, there are two problems current HTML editors. The first problem is that they’re little outdated. You may have noticed that I used the name “CKEditor” rather than “FCKEditor” in the title of this blog post.  This is because the latest version of the product has been renamed due to complaints about the original name (named after it’s creator but you may see the issue). The second problem with the current HTML editors has to do with how easy it is to exceed the current 32767 size limit for APEX items.

The good news is that APEX 4 already addresses the first problem for sure. While it will be possible to add the old HTML editors, the new version will be available too, along with a handy option that allows you to easily choose between several different themes. The second problem, that of the size constraints, may also be going away in the near future. As I understand it, APEX was never really the “cause” of the problem, it was mod_plsql, the lower level plumbing so to speak. With the release of the new Java based listener, this should no longer be an issue so we may eventually see a resolution – we’ll just have to wait and see…

But for the impatient/hard working folks out there, I have good news: you don’t have to wait for APEX 4 to be released to use the new CKEditor or move CLOBs to and from an APEX page and the DB. And while the steps involved to get this functionality now are not as simple as just using a built in item type, you’ll find you have more control than normal which is always beneficial.

The following sections detail some of the prep involved as well as the techniques I use to deal with the two main processes surrounding CLOBs: moving them from the browser to the database and moving them from the database to the browser. There are many different ways to go about implementing this functionality, this is just one. Here’s a demo if you’d like to check it out.

Prep

1. Download the new CKEditor files, unzip them, then move them to a file server that you have access to. Only one file need be included to make CKEditor available to your application (example in JavaScript process below). I recommend you do this at the page level rather than at the application level to avoid unnecessary page weight.
   
   Note: When I first did this I got some JavaScript errors and found the cause to be a BOM used in the source files. I used notepad++ to remove them from the relevant files (I can provide more details if others have the same issue).
2. Have jQuery and jQueryUI available to the application. The code below uses Google’s hosted files. I also use the BGI Frame plugin.
3. Add an item to the page where you want the editor to appear. The type should be Display as Text (does not save state). Leave all the defaults but remember the name. In the example code below, the name of the item is PXX_CLOB.

CLOB – From Browser to DB

Typically, a user clicks a submit button which submits the page for processing. The APEX engine then executes the various computations, validations, and processes that have been added to the page before branching to another. When working with CLOBs, we need to add a detour before the pages is submitted.

The “extra stop” is actually an AJAX process which moves data from the web page to the database before the page is submitted. A while ago this required quite a bit of code to accomplish, but Carl Backstrom once introduced a generic set of utilities that made the task much easier.  As far as I know, they’re still the best means to move a CLOB on and off a page in APEX.

The basic concept is to use a JavaScript object (used to encapsulate the AJAX process) which moves the CLOB from the page to an APEX collection named CLOB_CONTENT. The collection will have one member and the value of the CLOB will be in the column named CLOB001. When the transfer of data from the page to the database is complete, the page is then submitted for processing. This change may necessitate modifications to page processes as the CLOB value will be in the collection rather than in the item itself.

Here’s a look at some sample JavaScript to handle the AJAX process. This includes references to required external files and code related to initializing the page (more on that below). I added additional comments to better explain the code.

The main JavaScript function responsible for page processing is named submitPage. This function can be called by setting the URL redirect for a button to the following (actual parameter will vary depending on the request):

Here is an example DML processes that works with the CLOB in the collection:

CLOB – From DB to Client

If you examined the JavaScript from above you may have noticed that some of the code was related to initializing the page. This code utilizes the same concepts from the previous part, only data is being moved from the APEX collection (CLOB_CONTENT) to the page.

For this reason we must properly populate that collection when the page loads – before the AJAX process fires. I usually go about this one of two ways:

1. Populate the collection with the value I want to work with – if such a value exists.
   
2. Initialize a "NULL" collection (to avoid NO_DATA_FOUND errors).