Oracle中表列由VARCHAR2类型改成CLOB

外热呃

情景

原来表中的列定义成VARCHAR2类型，众所周知，VARCHAR2类型最大支持长度为4000。如果由于业务需要，想把此列转换为CLOB类型，在Oracle中直接通过ALTER语句转换是行不通的。下面根据具体事例讲解在Oracle数据库中如何把表列由VARCHAR2类型转换为CLOB类型。


示例准备

1. 新建两张张表TB_WITHOUT_DATA（此VARCHAR2列不包含数据）和TB_WITH_DATA（此Varchar2列包含数据)



    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;  


错误方法



    ALTER TABLE TB_WITHOUT_DATA MODIFY description clob;  

错误信息：

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.



解决方法
方法一：对于此列没有数据的可通过以下方法修改-首先把该列改成Long类型，然后再改成clob类型



    alter table TB_WITHOUT_DATA modify description long;--首先改成Long类型  
    alter table TB_WITHOUT_DATA modify description clob;--在Long类型的基础上改成clob类型  

注：对于此列已经存在数据的，不能通过此方法，否则会报如下错误：



    alter table TB_WITH_DATA modify description long;--更改包含数据的列  



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


方法二：此方法适合此列包含数据和此列不包含数据两种情况

步骤一：把原来表中该列重命名



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

步骤二：在表中增加该列，并指定改列类型为clob



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


步骤三：对此列包含数据的需要包数据从步骤一重命名列中拷出(对于此列没有数据的此步骤省略)



    update TB_WITH_DATA set description=description_bak;  
    commit;  


步骤四：删除步骤一中的备份列



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


步骤五：验证

1) 表结构验证



    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) 数据验证



    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   



方法三：此方法适合此列包含数据和此列不包含数据两种情况

在讲解方法三之前，需要包表恢复到准备阶段，由于时间关系，直接通过drop然后re-create方法,脚本如下：



    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;  


步骤一：重命名两张表



    rename TB_WITHOUT_DATA to TB_WITHOUT_DATA_BAK;  
    rename TB_WITH_DATA to TB_WITH_DATA_BAK;  


步骤二：创建两张新表（通过以下语句创建两张表）



    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;  


表结构与数据验证：



    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   


步骤三：删除备份表：



    DROP TABLE TB_WITHOUT_DATA_BAK;  
    DROP TABLE TB_WITH_DATA_BAK;