ORA-22858: 数据类型的变更无效 varchar2类型转换为clob类型

oracle中，如果一个列的类型为varchar2，那么它不能直接转换为clob类型。可以通过间接的方式来修改。下面为实验：

SQL>create table test(id int,name varchar2(10));

Tablecreated

SQL>insert into test values(1,'a');

1row inserted

SQL>insert into test values(2,'b');

1row inserted

SQL>commit;

Commitcomplete

SQL>select * from test;

    ID   NAME

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

  1      a

  2      b

 SQL> desc test

NameType         Nullable Default Comments

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

ID   INTEGER     Y                        

NAMEVARCHAR2(10) Y                        

 

SQL> alter table test modify(name clob);

 altertable test modify (name clob)

 ORA-22858: 数据类型的变更无效

 如果直接转换，报错！

我们可以通过间接的方式来操作：

新建一个clob类型的列，将原列的值插入新建的列，然后删除原列，重命名新列。

 

SQL> alter table test add name1clob;

 Table altered

 SQL> update test setname1=name;

 2rows updated

 SQL> select * from test;

 

                                     IDNAME       NAME1

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

                                      1 a          a

                                      2 b          b

 

SQL> desc test

Name  Type        Nullable Default Comments

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

ID    INTEGER     Y                        

NAME  VARCHAR2(10) Y                        

NAME1CLOB         Y                        

 

SQL> commit;

Commit complete

SQL> alter table test dropcolumn name;

Table altered

SQL> alter table test renamecolumn name1 to name;

Table altered

SQL> select * from test;

                                     ID NAME

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

                                      1 a

                                      2 b

SQL> desc test

NameType    Nullable Default Comments

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

ID   INTEGER Y                        

NAMECLOB    Y           

如需转载，请注明出处：http://blog.csdn.net/nanaranran/article/details/17281519