The table column in Oracle is changed from VARCHAR2 type to CLOB

Scenario

The columns in the original table are defined as VARCHAR2 types, and it is well known that the maximum support length for the VARCHAR2 type is 4000. Assume that because of business needs. To convert this column to the CLOB type, it is not feasible to convert directly through the ALTER statement in Oracle. The following is a detailed example of how to convert a table column from a VARCHAR2 type to a CLOB type in an Oracle database.

Demo Sample Preparation

1. New two sheets tb_without_data (this VARCHAR2 column does not include data) and Tb_with_data (this Varchar2 column includes data)

create table TB_WITHOUT_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

create table TB_WITH_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');
insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');
insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');
insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');
insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');
insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');
commit;

Error Method

ALTER TABLE tb_without_data MODIFY description Clob;

Error message:

SQL error:ora-22858:invalid Alteration of datatype
22858.00000-"Invalid alteration of datatype"
*cause:an attempt was made to modify the column type to object, REF,
Nested table, Varray, or LOB type.
*action:create a new column of the desired type and copy the current
Column data to the new type using the appropriate type
Constructor.

Workaroundmethod One: There is no data for this column can be changed by the following method-first change the column to a long type, and then change to the CLOB type

Alter table TB_WITHOUT_DATA modify description long;--first changed to Long type
Alter table TB_WITHOUT_DATA modify description clob;--based on the Long type to change to clob type

Note: Data already exists for this column. This method cannot be used. Otherwise, such as the following error is reported:

ALTER TABLE Tb_with_data Modify description long;--change columns that include data

SQL Error:ora-01439:column to is modified must is empty to change datatype01439. 00000-  "column to be modified must is empty to change datatype"

method Two: This method is suitable for this column including data and this column does not include data in both cases

Step One: Rename the column in the original table

alter table TB_WITHOUT_DATA rename column description to description_bak;
alter table TB_WITH_DATA rename column description to description_bak;

Step Two: Add the column to the table and specify the column type to CLOB

alter table TB_WITHOUT_DATA add description clob;
alter table TB_WITH_DATA add description clob;

Step three: The required package data for this column including data is copied from the Step one renamed column (omitted for this step without data for this column)

update TB_WITH_DATA set description=description_bak;
commit;

Step four: Remove the backup column from step one

alter table TB_WITHOUT_DATA drop column description_bak;
alter table TB_WITH_DATA drop column description_bak;

Step Five: Verify

1) Table Structure Verification

DESC TB_WITHOUT_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB 

DESC TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB  

2) Data validation

select * from TB_WITH_DATA;

        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie                       

 6 rows selected 

method Three: This method is suitable for this column including data and this column does not include data in both cases

Before explaining the method three, the package table needs to be restored to the prep stage, because the time relationship, directly through the drop and then the re-create method, scripts such as the following:

drop table TB_WITHOUT_DATA;
drop table TB_WITH_DATA;

create table TB_WITHOUT_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

create table TB_WITH_DATA
(
  id NUMBER,
  name VARCHAR2(100),
  description VARCHAR2(2000)
);

insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');
insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');
insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');
insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');
insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');
insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');
commit;

Step One: Rename two tables

rename TB_WITHOUT_DATA to TB_WITHOUT_DATA_BAK;
rename TB_WITH_DATA to TB_WITH_DATA_BAK;

Step II: Create two new tables (create two tables with the following statement)

create table TB_WITHOUT_DATA
as
select id, name, to_clob(description) description
from TB_WITHOUT_DATA_BAK;

create table TB_WITH_DATA
as
select id, name, to_clob(description) description
from TB_WITH_DATA_BAK;

Table structure and data validation:

desc TB_WITHOUT_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB

desc TB_WITH_DATA
Name        Null Type          
----------- ---- ------------- 
ID               NUMBER        
NAME             VARCHAR2(100) 
DESCRIPTION      CLOB 

select * from TB_WITH_DATA;

select * from TB_WITH_DATA;

        ID NAME                       DESCRIPTION                                     
---------- -------------------------- ------------------------------------------------
         1 David Louis                He is capable of resolving such kind of issue   
         2 German Noemi               She is very beatiful and charming               
         3 Oliver Queen               He is main actor in the Green Arrow             
         4 Mark Williams              He plays snooker very well                      
         5 Sita Rama Raju Kata        I do not know this guy                          
         6 Promethus                  This is a very nice movie                       

 6 rows selected 

Step three: Delete the backup table:

DROP TABLE TB_WITHOUT_DATA_BAK;
DROP TABLE TB_WITH_DATA_BAK;

--------------------------------------------------------------------------------------------------------------- --------------------------------------------

Let's say that you're having trouble trying, or that my code is in the wrong place. Please give my correct correction. Thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source.

The table column in Oracle is changed from VARCHAR2 type to CLOB