Oracle中表列由VARCHAR2类型改成CLOB_mrdavidtian的博客

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

从未表白的脆皮肠 · 智能清潔座：百靈專用匣式清潔液| 百靈· 4 月前 ·

傻傻的草稿纸 · 魔兽世界70怀旧服盗贼有什么优势作用?_游戏易站· 4 月前 ·

谈吐大方的电池 · 梦工厂动画公司（DreamWorks ...· 4 月前 ·

爱跑步的豆芽 · 黃慎的書畫藝術研究__臺灣博碩士論文知識加值系統· 5 月前 ·

活泼的作业本 · 量子型领导对员工越轨创新行为的影响：以组织自 ...· 5 月前 ·

原来表中的列定义成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
select id, name, to_clob(description) description
from TB_WITHOUT_DATA_BAK;
create table TB_WITH_DATA
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 
版权＠：转载请标明出处！ 
                                    1、和VARCHAR不同的是，VARCHAR2把空串等同于null处理，这也是为什么ORACLE推荐使用VARCHAR2类型的原因。存储大小是输入数据的实际长度加 2 个字节，用于反映存储的数据的长度。例如varchar2(3 char)，可以存储3个字符（包括字母和汉字），如'啊啊啊'、'aaa'、'啊aa'，与字节（byte）长度无关；与上面不同的是，是根据Unicode标准所进行的定义的类型，通常用于支持多国语言类似系统的定义。固定长度，非 Unicode 字符数据，长度为 n 个字节。
第一步:首先改成Long 字段
alter table pwlp_accuse_info modify INSTRUCTION_STYLE long;
第二步:.然后改成clob 字段
alter table pwlp_accuse_info modify INSTRUCTION_STYLE clob;
                                    1、clob字段转varchar字段主要用到dbms_lob.substr方法，该方法有三个参数，分别是截取的clob字段、截取长度以及起始位置，其中字段名为必须的，截取长度以及其实位置可以根据需要使用。首先创建测试表，表中包含三个字典，标识码、clob测试字段和varchar测试字段。在表中插入一条测试数据，其中clob和varchar字段默认都为空。dbms_lob.substr(字段名，长度，起始位置)设置clob字段的值为f_str字段的值。设置f_str的值为clob字段的值。
                                    在之前的表设计中，是使用的 varchar2，由于字段内容太长了，导致生产报错了，varchar2 仅支持 4000 ，我们需要改成更大的 CLOB注意事项1. 在Oracle JDBC中采用流机制对 BLOB/CLOB 进行读写操作，所以要注意不能在批处理中读写 BLOB/CLOB字段，否则将出现Stream type cannot be used in batching 异常。2. Oracl...
commit;
alter table fab_policy_his modify (fabstract long);
alter table fab_policy_his modify (fabstract clob);
delete　from fab_policy;
commit;
alter table fab_po
                                    模拟情景，表：batchintfloadlog，要修改字段：resultinfo，字段 从原来的 varchar2 修改为 clob
1、假设要修改字段数值为空，则可以直接修改；
可是发现如下错误：
SQL> alter table batchintfloadlog modify (resultinfo clob);
alter table batchintfloadlo...
SQL> desc test
			Name                   Null?  Type
			----------------------------------------- -------- ----------------------------
			ID                ...
目前2个表的字段类型
alter table TECHINFOA modify (TECHD VARCHAR2(4000));
alter table KNOWLEDGEP modify (PCONTENT VARCHA
                                         近日工作中发现有一张表的字段类型建错了，本应是BLOB类型却被别人建成了VARCHAR2(200)，修改时oracle却提示“ORA-22858 invalid alteration of datatype”错误。经查看相关资料才了解，是oracle不允某些类型的字段修改。具体就是不可以将字段类型修改为：object、REF、nested table、VARRAY、 CLOB、B