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.
- Have jQuery and jQueryUI available to the application. The code below uses Google’s hosted files. I also use the BGI Frame plugin.
- 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.
Here is an example DML processes that works with the CLOB in the collection:
CLOB – From DB to Client
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:
- Populate the collection with the value I want to work with – if such a value exists.
- Initialize a "NULL" collection (to avoid NO_DATA_FOUND errors).