Modifying a column from VARCHAR2 to CLOB in a table in Oracle

Today I had to modify columns in a table of which I have them in VARCHAR2 and I want to move them to CLOB, for that reason I make this post because it may be that someone also needs it. If someone knows a better method please share it!

First of all let's look at these two data types. In Oracle, VARCHAR2 and CLOB data types are different in terms of their storage capacity and their way of manipulation.

• VARCHAR2 is a data type used to store variable length character strings in the Oracle database. The maximum length of a VARCHAR2 value in Oracle is 4000 bytes. If we need to store a longer string, we can use the CLOB data type.
• CLOB is a data type used to store large amounts of character data in the Oracle database. CLOB values can store up to 128 terabytes of data. CLOBs can also store Unicode characters and allow the use of multi-byte characters. CLOB manipulation is performed by special functions such as DBMS_LOB that provide an API for accessing CLOB data.

Let's see what are some of the additional differences between VARCHAR2 and CLOB:

1. Storage size: VARCHAR2 is limited to 4000 bytes of storage, while CLOB can store large amounts of data (up to 128 terabytes).
2. Data manipulation: VARCHAR2 values can be manipulated by standard string operations such as SUBSTR, INSTR and LENGTH, while CLOBs require special data manipulation functions such as DBMS_LOB.
3. Indexes: VARCHAR2 can be indexed with standard indexes, while CLOB indexes are handled differently and special full-text indexes are used.
4. Performance: VARCHAR2 is generally faster than CLOB, especially in operations involving string searches and comparisons. CLOBs require more storage and processing resources.

The CLOB data type is not compatible with the VARCHAR2, that is why we cannot directly change the data type, but also we cannot change the type of a table column if that column has data.

To do this, first of all we make a backup copy of the table we are going to work with :).

These are the steps I have taken to achieve this:

• I have created a new column of type CLOB in the table
• Then I copied the values from the VARCHAR2 column to the new CLOB column and verified that the data is correct.
• Next, delete the original VARCHAR2 column.
• And finally I have renamed the new column CLOB with the name of the original column VARCHAR2

Let's see an example of this operation:

-- Add a new column of type CLOB 
ALTER TABLE my_table ADD my_clob CLOB; 

-- Copy the values of the VARCHAR2 column to the new column 
CLOB UPDATE my_table SET my_clob = my_varchar2; 

-- Delete the original VARCHAR2 column 
ALTER TABLE my_table DROP COLUMN my_varchar2; 

-- Rename the new column CLOB with the name of the original VARCHAR2 column 
ALTER TABLE my_table RENAME COLUMN my_clob TO my_varchar2;

It is important to note that this process can be slow and resource-intensive if the table is large. In addition, the table may need to be idle during the conversion process, which may affect system availability. Therefore, I recommend you to perform this process carefully and in a test environment before applying it in a production environment 🙂

I hope it will be useful!

Update

It turns out and happens that my desire to learn more and more every day has been heard! By publishing this post on Twitter my friend Javier Morales he told me that there is a much better way to perform this conversion and even hot without leaving latencies because I am in a moment removing the column and renaming, so I want you to see this video where he tells me a spectacular way to perform this conversion from varchar2 to clob that will leave you with your eyes open!