CLOB in APEX Rich Text Editor

It’s been a while since the last time I posted something, but there were no new challenges, which always inspire me to share the solutions with you, until last week. I was tasked to build dynamic surveys, where users can define their own questions. This resulted into decision of building a function in a package, which returns HTML of the survey in CLOB. In another application we already used load and save CLOBs so I reused the code and behold, I got apex.ajax.clob is not a constructor error. What caused the error and how to solve it is what I’ll be talking about today. If you are interested about an article about surveys, I might do it in future, but now I’ll focus more on the standard use of CLOB column in application and that’s using it together with APEX Rich Text Editor.

If you want to include rich text editor in your application, CLOB is the answer where you want to store your text, if you know, there will be a longer text saved. If you choose VARCHAR2 as a column, you are limited to 32k size, which might seems quite a lot, but not if you are counting all the formatting in HTML, which saves alongside with your text. You quickly might realize, you overflow this size, so saving to CLOB from the get-go save you these troubles. On the other hand you cannot work with CLOB simply like you are working with page items. There are other limitations, like 32k size of http request, which then has to be chunked and so on.

All this might result into looking on the Internet and searching for a way, how to solve loading and saving CLOBs. You might find the article from Dan McGhan, it’s 10 years old, but for most versions of the APEX it’s still working. The important part in it is the most versions. From the APEX 19+ some of the JavaScript features were discontinued and desupported and the apex.ajax.clob is one of them. This in a loop returns me to the beginning of this article, where I mentioned the error for this JavaScript function. You can still get around it and either on the page itself include JavaScripts from 18.x versions of APEX, or you can go to Shared Components > User Interface Attributes > User Interface Details > JavaScript and check the box next to Include Deprecated or Desupported JavaScript Functions for 18.x to turn it on for the whole application. This is the easiest solution, but you don’t want to carry on and support old JavaScripts from now on, so after this long introduction, let’s start exploring different approach.

Getting CLOB from DB to page

If you want to save CLOB into a database, you firstly need to display it on the page. The prerequisites for this are simple – you have a table with the clob column and ID column – something like:

CREATE TABLE clob_table(
    ID_COLUMN NUMBER NOT NULL
  , CLOB_COLUMN CLOB
);

On the page (let’s say page 1) you’ll have two page items:

  • P1_ID_COLUMN – this page item will be hidden for distinguish with which row of the clob_table you are working
  • P1_CLOB_COLUMN – this page item will be type of rich text editor, you can define all sorts of options there (like toolbars options and so on), the main option is, that the source for this page item is null.

With this out of the way, let’s begin getting the CLOB from the database. For this to happen you’ll need two things – JavaScript, which will run on page load and an Ajax Callback process.

CKEDITOR.on("instanceReady", function(event) {

  // Run the Ajax Callback process
  var p = apex.server.process('GET_CLOB', {
    pageItems:['P1_ID_COLUMN']
  }, {
    dataType: 'html'
  });

  // When the process is done, set the value to the page item
  p.done(function(data) {
    apex.item('P1_CLOB_COLUMN').setValue(data);
  });

});

Dissecting the code little bit:

  1. On page load – you wait for the CKEDITOR (APEX rich text editor) instance to be ready, so you won’t load the CLOB before you have editor window to put it in.
  2. You run the mentioned Ajax Callback process called GET_CLOB and post parameter P1_ID_COLUMN with it.
  3. When the process is finished, you set the page item P1_CLOB_COLUMN with the returned value – this will result into loading the data into your rich text editor window.

Now for the GET_CLOB process:

DECLARE
  l_clob        CLOB;
  l_file        BLOB;
  l_dest_offset PLS_INTEGER := 1;
  l_src_offset  PLS_INTEGER := 1;
  l_lang_ctx    PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  l_csid        PLS_INTEGER := NLS_CHARSET_ID('UTF8');
  l_blob_warn   PLS_INTEGER;

BEGIN

  BEGIN

    -- Get the CLOB
    SELECT NVL(clob_column, EMPTY_CLOB())
    INTO l_clob
    FROM clob_table
    WHERE id_column = :P1_ID_COLUMN;

  -- Exception handling when no data were found for the ID (you are creating new row
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_clob := EMPTY_CLOB();
      
  END;

  -- If we don't have a CLOB, just return empty content
  IF (DBMS_LOB.GETLENGTH(l_clob) = 0) THEN
    HTP.P('');
  ELSE

    -- Create BLOB from CLOB
    DBMS_LOB.CREATETEMPORARY(  lob_loc => l_file
                             , cache => true
                             , dur => DBMS_LOB.CALL);
    DBMS_LOB.CONVERTTOBLOB(l_file, l_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset, l_csid, l_lang_ctx, l_blob_warn);

    -- Download BLOB
    OWA_UTIL.MIME_HEADER('text/html', false);
    HTP.P('Content-Length: ' || dbms_lob.getlength(l_file));
    HTP.P('Content-Disposition: attachment; filename="content.html"');
    OWA_UTIL.HTTP_HEADER_CLOSE();
    WPG_DOCLOAD.DOWNLOAD_FILE(l_file);

  END IF;

END;

Again few words about the block of code:

  1. We get the CLOB value* into a l_clob variable, here we’re also handling the error, when no data were found (e.g. creating new rows, so there is no record for that row in the table and the P1_ID_COLUMN page item is empty) and in that case creating empty CLOB.
  2. We then create BLOB from the CLOB (if we have data, otherwise just return empty response) as we want to download it on the page, and the WPG_DOCLOAD.DOWNLOAD_FILE procedure accepts only BFILE or BLOB as a parameter.
  3. We initialize the file download with WPG_DOCLOAD.DOWNLOAD_FILE which then is returned and used by the JavaScript part.

* The NVL(clob_column, EMPTY_CLOB()) is there, if the clob_column exists and is NULL to avoid the sqlerrm:ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 error.

With this JavaScript and Ajax process you can get CLOB out of the database and inject it into the page. If your only goal is to do that, you can update the code to serve your purpose and you are done. For the rest of you, who want to edit the data (in this example change the content of the rich text editor) and save it back to the database, you have more to read.

Setting CLOB from page to DB

Now with the data on the page, you’ll have to somehow save it to the database. This time it’s more complicated, so you’ll need JavaScript function, an Ajax Callback process and normal PL/SQL process on page processing step. You’ll also need a button to submit the page.

Let’s start from the button, we’ll call it SAVE. Because we need to run the Ajax Callback process before the page submit its action will be defined by dynamic action. This dynamic action will execute JavaScript code below.

// Function to send CLOB to process
function sendClob(call, array, request) {
  if (array[call-1] || call === 1) {
      var p = apex.server.process('SET_CLOB', {
       x01: call
     , x02: array[call-1]
    }, {
      dataType: 'html'
    });
    p.done(function(data) {
      sendClob(call + 1, array, request);
    })
  } else {
    apex.submit(request);
  }
}

var array = apex.item('P1_CLOB_COLUMN').getValue().match(/[\s\S]{1,20000}/g) || [];
sendClob(1, array, 'SAVE');

This code needs little bit more explanation:

  1. Starting with the code below the function sendClob – we get the value of the rich text editor item, we split the string it into an array of strings with limit of 20 000 characters (or empty array). This is to get around the 32k limitation of the http request.
  2. We then call the sendClob function. The parameters are call identifying the call number of the function, array (the whole string of the rich text editor split into the array by the previous step and request – as the request we will be using to submit after we are done with the upload.
  3. The function sendClob itself the process all the data. It uses recursion, where it runs the Ajax process SET_CLOB with parameters x01 (this sends the call number) and x02 (string from the array on position call – 1).
  4. After the first call is done, it automatically runs another call. Each time the function sendClob runs, before running the process SET_CLOB it checks if we are actually sending any data or if it’s the first call of the function. If not, we are at the end of the array, it will run the apex.submit process to submit the page with parameter provided to the function.

In the SET_CLOB Ajax process we’re using the APEX_COLLECTION to temporarily store the CLOB in database.

DECLARE
  l_src CLOB;
  l_trg CLOB;
BEGIN

  IF (APEX_APPLICATION.G_X01 = 1) THEN

    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'CLOB_CONTENT');

    APEX_COLLECTION.ADD_MEMBER (  p_collection_name => 'CLOB_CONTENT'
                                , p_clob001 => APEX_APPLICATION.G_X02);

  ELSE

    DBMS_LOB.CREATETEMPORARY(  lob_loc => l_trg
                             , cache => true
                             , dur => DBMS_LOB.CALL);

    SELECT clob001
      INTO l_src
      FROM apex_collections
     WHERE collection_name = 'CLOB_CONTENT';

    DBMS_LOB.APPEND(l_trg, l_src);
    DBMS_LOB.APPEND(l_trg, APEX_APPLICATION.G_X02);

    APEX_COLLECTION.UPDATE_MEMBER(  p_collection_name => 'CLOB_CONTENT'
                                  , p_seq => '1'
                                  , p_clob001 => l_trg);

  END IF;

END;

This process is quite straight forward:

  1. If the x01 is 1, it means it’s the first call of the process, we create or truncate the APEX_COLLECTION named CLOB_CONTENT and add the x02 value into the CLOB column of the collection.
  2. Every subsequent call of the process will result in getting the CLOB value of the CLOB_CONTENT collection, appending it to the new CLOB, where we append the next part of the string sent as another x02 parameter. With this new CLOB we update the CLOB column in the CLOB_CONTENT collection.

Finally, after all the Ajax processing is done, the page is submitted with the apex.submit function. Now all the page validations run, all the other processing on the page submit and with it, we run our last process:

BEGIN

  MERGE INTO clob_table ct
  USING (SELECT :P1_ID_COLUMN AS id
              , clob001 AS clob
           FROM apex_collections
          WHERE collection_name = 'CLOB_CONTENT') ac ON (ct.id_column = ac.id)
   WHEN MATCHED THEN UPDATE SET ct.clob_column = ac.clob
   WHEN NOT MATCHED THEN INSERT (ct.clob_column) VALUES (ac.clob);

END;

With this last process we save the CLOB content of the APEX_COLLECTION to the database. You can either use separate insert and update statements based on existence of value in item P1_ID_COLUMN or you can do it in one step as shown above with the MERGE INTO statement.

If you scroll up, you’ll notice, we run the SET_CLOB process even if we have no data to sent. That’s to ensure we actually create and truncate the collection, so don’t need to check in the submit process if the collection exists or if we have there some old data from the previous updates and so on.

This code above is by far not perfect, it might need some minor adjustments here and there, but it works and it doesn’t need the old JavaScripts which are not supported anymore, so hope it’ll help you a little bit.

You may also like

8 Comments

  1. When I use the above JS code on page load I get and error as CKEDITOR not defined.

    Also I have a question about exactly where does one use this JS and Ajax. I have a report(with id,clob column) which also shows content from the clob column.

    When the clob column has a very large value, I observed that it cuts off some of the value and does not show full clob content on report page

    When user clicks on ID link from the report page, it would open the respective row in and edit mode. For some of the rows when I open it in edit mode, I get below error

    ORA-06502: PL/SQL: numeric or value error
    component.type: APEX_APPLICATION_PAGE_PROCESS

    Do one needs to use JS and Ajax on both report and form page as well, or any one of them?

    1. Hi, this article is about the Rich Text Editor page item and using CLOB column in database with it. That’s where the “CKEDITOR” is coming from. If you want to use the code for the report, you’d probably have to adjust it yourself. I’m not sure, how to do that – I didn’t looked at this, to be honest, because I cannot imagine updating report with multiple CLOBs.

  2. Hi your code works great with one editor but not with 2 editors on same page it through Uncaught Error Editor not found on page load

    CKEDITOR.on(“instanceReady”, function(event) {

    I want it to run only for 1 CK editor not the other one how can I achieve this?

    1. Sorry, this is intended for only one editor on the page, so I don’t have a clue, how it behaves on multiple editors.

  3. Thank you for sharing. Works beautifully.

    Just one typo, take out the extra comma.

    APEX_COLLECTION.UPDATE_MEMBER( p_collection_name => ‘CLOB_CONTENT’
    , p_seq => ‘1’
    , p_clob001 => l_trg);

  4. Thanks for this updated approach to CLOB’s and Rich Text Editor. Do you have the sample Apex application used above available anywhere to download or view?

    1. I didn’t make a page for this yet. I might do it in the future, but currently life and work tend to go in a way, so I have no time. Though we are using this in our application on multiple pages and it seems so far like it’s working without any problem.

Leave a Reply to Barry Cancel reply

Your email address will not be published. Required fields are marked *