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

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 datatype01439. 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_DATAName        Null Type          ----------- ---- ------------- ID               NUMBER        NAME             VARCHAR2(100) DESCRIPTION      CLOB 

DESC TB_WITH_DATAName        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_DATAasselect id, name, to_clob(description) descriptionfrom TB_WITHOUT_DATA_BAK;create table TB_WITH_DATAasselect id, name, to_clob(description) descriptionfrom TB_WITH_DATA_BAK;

表結構與資料驗證：

desc TB_WITHOUT_DATAName        Null Type          ----------- ---- ------------- ID               NUMBER        NAME             VARCHAR2(100) DESCRIPTION      CLOBdesc TB_WITH_DATAName        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;

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

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：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類型等等的轉換