How to Modify VARCHAR2 to CLOB of a Column

  • by

Major Data Types

There're several major data types built in Oracle.

  • Character
  • Numeric
  • Datetime
  • LONG
  • RAW
  • LOB (Large Object)

In each major data types, minor data types can be changed easily from one to another using ALTER TABLE MODIFY statement, as long as they are in the same major data types.

For example, we'd like to change a column FIRST_NAME from VARCHAR2 into CHAR. The original table definition is as the followings:

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
...

We convert FIRST_NAME from VARCHAR2 to CHAR.

SQL> alter table hr.employees modify (first_name char(20));

Table altered.
SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 FIRST_NAME                                         CHAR(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
...

It looks pretty easy to convert one character type to another.

VARCHAR2 to CLOB

Let's see another case. Since the maximum length of VARCHAR2 is 4000 bytes for 11g and earlier releases, we'd like to convert a VARCHAR2 to CLOB (Character Large Object) so as to raise its length upper limit.

SQL> alter table hr.locations modify (city clob);
alter table hr.locations modify (city clob)
                                 *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

We hit a wall.

How to Convert VARCHAR2 to CLOB

No, we can't do it directly, but we can do it indirectly. Let's see steps.

1. Add a Target Column with CLOB.

SQL> alter table hr.locations add (city_1 clob);

Table altered.

We appended _1 to the new column name to differentiate from the source column.

2. Migrate Data from VARCHAR2

SQL> update hr.locations set city_1 = city;

23 rows updated.

SQL> commit;

Commit complete.

Check the new table definition.

SQL> desc hr.locations;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOCATION_ID                               NOT NULL NUMBER(4)
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(12)
 CITY                                      NOT NULL VARCHAR2(4000)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_ID                                         CHAR(2)
 CITY_1                                             CLOB

3. Add Constraint (Optional)

If the original column has imposed constrains, we should add them on the new column too, like we add NOT NULL constraint on the column as below.

SQL> alter table hr.locations modify (city_1 not null);

Table altered.

4. Drop the Source Column

Our plan is to replace the original column with the new column. Therefore, we need to drop the original column.

SQL> alter table hr.locations drop column city;

Table altered.

5. Rename the Target Column

Use RENAME clause to replace the original column.

SQL> alter table hr.locations rename column city_1 to city;

Table altered.

SQL> desc hr.locations;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOCATION_ID                               NOT NULL NUMBER(4)
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(12)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_ID                                         CHAR(2)
 CITY                                      NOT NULL CLOB

Now, you have to make sure that all business logic are running correctly.

Leave a Reply

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