Recently, I had an interesting case in my project. The issue involved a simple table with several NUMBER/VARCHAR2 columns and a CLOB column. This column was intended for very long texts, such as prose, XML, or JSON.

As a result of my task, I needed to update one of the rows in this table, specifically in the CLOB column. The new text was quite long, and notably, it exceeded the length of even the extended version of VARCHAR2(32767).

Initially, I approached the problem in the usual way to prepare a script for executing this UPDATE, but I quickly encountered issues:

A regular UPDATE setting the column value doesn't work - my text is too long.
Anonymous block, procedure with a CLOB parameter - same issue.
Using the TO_CLOB function or concatenation operator - also unsuccessful.

I started searching the Oracle Forum on this topic and found several threads about it. Some were marked as solved. Generally, the accepted solution involved splitting the long text into smaller pieces that could fit into VARCHAR2 variables. The next step was to incrementally append to a CLOB variable, which could then be used in our UPDATE statement.

Initially, I approached the presented ideas with enthusiasm - after all, they give us the desired effect. However, one issue remained unresolved - I had to divide the text into smaller parts on my own. This meant carefully analyzing how long my current chunk was, where I left off previously, and where to continue next.

💡

If I'm not enlightened enough, please let me know. Perhaps I'm missing something obvious

Solution

I think one of the best things to do in this situation is to build an APEX interface. A simple Report and Form will work fine. For several versions now, APEX has supported the CLOB type for some page items. As shown in the screenshot, I use a Textarea to enter a new value, which I can configure in many ways. At this point, the most important part for us is the Source section, where I can set the Data Type to CLOB. This changes the game.

The Interactive Report column cannot handle such long texts. However, you can display a selected text fragment and the total number of characters as additional information.

select ID,
       FIRST_NAME,
       LAST_NAME,
       dbms_lob.substr(TEMPLATE, 100, 1) as template, -- up to 100 characters
       dbms_lob.getlength(template)      as template_length
  from HR_TEMPLATES

A simple form might look like this - it's very basic, but you can use APEX settings to make it more sophisticated: