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;
-----------------------------------------------------------------------------------------------------------------------------------------------------------
假设您们在尝试的过程中遇到什么问题或者我的代码有错误的地方。请给予指正。很感谢!
联系方式:david.louis.tian@outlook.com
版权@:转载请标明出处。
最新文章
- text-align:justify的使用
- Unity3D Multi-Compile Shader
- 51Nod 1136 欧拉函数 Label:数论
- 杭电ACM 1013 Digital Root
- 【Asp.Net】document.getElementById 的属性介绍
- Python学习笔记_Chapter 4数据保存到文件
- 基于ssh,shell,python,iptables,fabric,supervisor和模板文件的多服务器配置管理
- hdu_1015(dfs)
- [Codeforces]605E Intergalaxy Trips
- mongoDB基本使用(二)
- CSS引入
- 基于Python+Django重定向的例子
- Centos7下GlusterFS 分布式文件系统环境搭建
- 使用scrapy爬取百度股票
- 使用Python启动一个简单的服务器
- .Net实现微信公众平台开发接口(二) 之 “获取access_token”
- PAT A1015 Reversible Primes (20 分)——进制转换,质数
- Java 8 Optional类深度解析(转载)
- springBoot基本配置
- GDI+用PNG图片做半透明异型窗口