Thursday, February 11, 2010

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.


  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).


  1. Hello Dan.

    Nice example!

    I have a problem though. When I run the process, it is not showing the Editor, it just shows the "loading" box forever. If I misspell the name of the CLOB, it immediately shows the editor (with nothing in it).

    I have added a HTML region with the js things on an "page template body 3" - and before the form containing the field.

    I have also added the pl/sql procedure "get from DB" as an after header process. (in this one I display a status via htp.p - so i know is has been run).

    The page is beeing called from a report with a link.

    Can you guide me where to look?

    Best regards

  2. Hi Dan,

    Thanks a million for this posting. I finally got it to work now - after 2 weeks of struggling.

    It "just" took a little reading and a good nights sleep to figure it out.

    Have a very nice weekend
    Mette, DK

  3. Hi Dan,

    I've been looking for new posts from you, and I noticed you haven't updated your blog in over 2 months!!
    I've always enjoyed and learned from your posts (like jQuery!). Hope everything is well with you. I'll be looking for your new blogs, I hope shortly!


  4. Hi Dan,
    When I click on the button to retrieve the CLOB data from the database, I get a javascript error, 'CKEDITOR.instances.P90_CLOB is null or not an object'. Does this have something to do with the BOM errors you were receiving? I'm not sure why I'm getting the error message.
    Thank you,
    Tammy (73pixieGirl)

  5. Hi again...turns out I had the incorrect path to my loader image which turned out to be the cause for the CKEDITOR error (??). Now I'm not getting any data when I click the button to retrieve the CLOB. The loader image keeps spinning...sounds like the same problem the first poster is having.
    Thank you!

  6. Hi Dan !

    It´s a very .. very interesting solution!

    I used this sample on APEX 3.2, and i could insert with sucess a new clob information to database. Everthing works fine on an insert/create action.

    But if i try to edit , the text field is loaded in the form, but the editor toolbar is not displayed in the form.

    And it just shows the "loading" box forever.

    I saw in internet explorer status bar tha APEX page is completed displayed, but with errors ("inesperated call to access property or method").

    Could you or anyone make a suggestion about how to debug/analyze it?

    Best regards,

  7. Sergio,

    I suggest you use Firefox with firebug to get some more meaningful information about the error.

    Also, are you upgrading to 4.0 anytime soon? I'm halfway through a plug-in that will make this much easier. If so, email me.


  8. Hi Dan,

    Unfortunately, i need to continue with Apex 3.2.1, because RDBMS 9i is used by an EBS 11.5.9 that can not be upgraded now to 10g/11g.

    I will follow your suggestion to use firefox, and i will looking foward the time you will release your plug-in to Apex 4.0.

    Best regards and thanks for the help !


  9. Hi Dan,

    I have tried this, but when ever the clob over 32k it won't work.

    I have built this page on oracle apex if you want to test.

    The server will come back with "Bad Request

    The request could not be understood by server due to malformed syntax."


  10. Fadi,

    The test still works for me. Sounds like something is not configured right on your side. Please send me an email with the workspace/username/password that I can use to log into APEX.

    You can find my email in the blog.


  11. Just wanted to say this post was a life saver. Thank you.


  12. Dan - I appreciate your efforts - but have there been any updates to this issue since 4.1 came out? Since CKEditor is now included with APEX 4.1, I hate the thought of downloading and maintaining my own copy.

  13. Hi John,

    There are probably better ways to do this now but I haven't explored them. The direction I would go is to adjust the code to use the built in editor.

    I can explore this in the future when I find time.


  14. Hi Dan
    We are on Apex 4.1.For an application i was using ckeditor3 and fckeditor2.When i enter text in the rich text editor, it does introduce spaces.Because it looks like the tags for paragraphs are introduced automatically that introduce additional space...Is there a way to remove the spaces?

  15. Prasanna,

    As this has more to do with Ckeditor you might want to try Goggling on that product. Here are a couple links that may help: