ORA-22858: invalid alteration of datatype(varchar2转化clob)

古城微笑少年丶 2022-09-26 14:54 33阅读 0赞

author：skate

time：2010-11-30

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

**ORA-22858: invalid alteration of datatype**

由于业务的需要，今天研发同事问我在oracle里如何把table的varchar2类型转化为clob类型，其实标量类型的变量可以直接转换。

在'列'没有数据时,可以直接修改

alter table table\_name modify column\_name new\_databyte;

在有数据的时候，可以删除和新建列或者通过一个中间列来修改原列的类型

**alter table table\_name add column\_new number;**

**update table\_name set  column\_new=column\_old;**

**alter table table\_name drop column column\_old;**

**alter table table\_name rename column  column\_new to column\_old;**

例子：

查看现有表结构：

SQL> desc test\_skate;  
Name Type   Nullable Default Comments  
\---- ------ -------- ------- --------  
NAME CLOB   Y                          
ID   NUMBER Y

添加新列：

SQL> alter table test\_skate add id\_new varchar(50);

Table altered

备份原列值：

SQL> update test\_skate set id\_new=id;

1 row updated

删除原列

SQL> alter table test\_skate drop column id;

Table altered

把新列重命名为原列名：

SQL> alter table test\_skate rename column id\_new to id;

Table altered

SQL> desc test\_skate;  
Name Type         Nullable Default Comments  
\---- ------------ -------- ------- --------  
NAME CLOB         Y                          
ID   VARCHAR2(50) Y

这种方法是把新增加的字段重命名为要转换的字段，字段的顺序发生了变化，如果要求字段顺讯不变，那就还要使用  
原来的字段，思路是先把现在列的数据转移到新增加的列上，然后修改原列的类型，再把数据转移回来，对原列数据  
要更新两次。如果数据量比较大的话，会产生大量的undo和redo；也会产生大量的阻塞；如果想对现有系统影响最小  
那就采用表在线重定义的方式

**alter table table\_name  add cloumn\_new number;**

**alter table table\_name  modify cloumn\_old null;**

**update  table\_name  set cloumn\_new=cloumn\_old,cloumn\_old=null;**

**commit;**

**alter table table\_name  modify cloumn\_old number(10,2);**

**update table\_name  set cloumn\_old=cloumn\_new,cloumn\_new=null;**

**commit;**

**alter table  tb\_test  drop column cloumn\_new;**

**alter table  tb\_test  modify cloumn\_old not null;**

**select \* from  tb\_test ;**

例子：

查看现有表结构：  
SQL> desc test\_skate;  
Name Type          Nullable Default Comments  
\---- ------------- -------- ------- --------  
NAME CLOB          Y                          
ID   VARCHAR2(50)  Y                          
TEXT VARCHAR2(100) Y

SQL> select \* from test\_skate;

NAME     ID          TEXT  
\----- --------------------  
1       222                                                 
1       222                                                 
1       222                                                 
1       222

添加新列  
SQL> alter table test\_skate add id\_new varchar(100);

Table altered

编辑原列可以为null  
SQL> alter table test\_skate modify id null;

alter table test\_skate modify id null

ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> alter table test\_skate modify id not null;

Table altered

SQL> alter table test\_skate modify id null;

Table altered

把原列数据复制到新列  
SQL> update test\_skate set id\_new=id,id=null;

4 rows updated

SQL> commit;

Commit complete

更该原列的数据类型  
SQL>  alter table test\_skate modify  id number;

Table altered

把新列的数据复制到原列  
SQL> update test\_skate set id=id\_new,id\_new=null;

4 rows updated

SQL> commit;

Commit complete

删除新列  
SQL> alter table test\_skate drop column id\_new;

Table altered

编辑原列不为null  
SQL> alter table test\_skate modify id not null;

Table altered

查看现有结构  
SQL> desc test\_skate;  
Name Type          Nullable Default Comments  
\---- ------------- -------- ------- --------  
NAME CLOB          Y                          
ID   NUMBER                                   
TEXT VARCHAR2(100) Y

SQL>

**但是如果要把varchar2类型转化为log类型，就要用些特殊手段了**

例如：

SQL> create table test\_skate  
  2  (  
  3    name varchar2(4000)  
  4  )  
  5  tablespace TBS\_ARENA  
  6  ;

Table created

及时表为空，也不让更改

SQL> alter table test\_skate modify name clob;

alter table test\_skate modify name clob

ORA-22858: invalid alteration of datatype

这里可以借助long类型过度，如果记录为空，可以直接修改为LONG类型，在从long转化为clob，对于LONG类型，不管有没有数据存在，  
可以直接修改为CLOB类型

SQL> alter table test\_skate modify name long;

Table altered