Oracle中表列由VARCHAR2类型改成CLOB，varchar2clob

和通数据库htsjk.Com2019-03-07 23:01 来源:未知阅读:9802 评论 362 热度2

标签：Clob VARCHAR2 VARCHAR2改成CL

Oracle中表列由VARCHAR2类型改成CLOB，varchar2clob

情景

原来表中的列定义成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;

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

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

oracle怎变更varchar2类型的列为clob

前言：近期迁移工作中，有些表的列数据类型太小，如下即为一例
SQL> create table t_varchar2_to_clob(a varchar2(100));表已创建。SQL> insert into t_varchar2_to_clob values('zxy');已创建 1 行。SQL> commit;提交完成。--经测：varchar2的空或实列皆不能变更为clob类型
SQL> alter table t_varchar2_to_clob modify a clob;
alter table t_varchar2_to_clob modify a clob*第1 行出现错误:
ORA-22858: 数据类型的变更无效--如下采用过渡方法
SQL> create table t_varchar2_to_clob(a varchar2(10));表已创建。SQL> insert into t_varchar2_to_clob values('zxy');已创建 1 行。SQL> insert into t_varchar2_to_clob values('zxy1');已创建 1 行。SQL> insert into t_varchar2_to_clob values('zxy12');已创建 1 行。SQL> insert into t_varchar2_to_clob values('zxy123');已创建 1 行。SQL> commit;提交完成。SQL> select * from t_varchar2_to_clob;A----------zxyzxy1zxy12zxy123--添加一个新列
SQL> alter table t_varchar2_to_clob add b clob;表已更改。SQL> col a for a10
SQL> col b for a10SQL> r1* select * from t_varchar2_to_clobA B
---------- ----------zxyzxy1zxy12zxy123--把源列的数据迁移到新列
SQL> update t_varchar2_to_clob set b=a;已更新4行。SQL> commit;提交完成。--查看源与新列是否数据一致
SQL> select a,b from t_varchar2_to_clob;A B
---------- ----------zxy zxyzxy1 zxy1zxy12 zxy12
zxy123 zxy123--删除源列
SQL> alter table t_varchar2_to_clob drop column a;表已更改。--把新列改名为源列
SQL> alter table t_varchar2_to_clob rename column b to a;表已更改。--查看变更后的表
SQL> desc t_varchar2_to_clob;
名称是否为空? 类型
----------------------------------------------------- -------- --------------
--------------------
A CLOB小结：1,上述操作适用于开发......余下全文>>

oracle怎更改列属性，我想把CLOB类型更改为varchar2(20)类型，错误语句以下，修正

语句没有错，谢谢，但是有可能有些东西限制CLOB类型，ROWID类型等等的转换

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

评论暂时关闭