The table column in Oracle is changed from VARCHAR2 type to CLOB

Scenario

The columns in the original table are defined as VARCHAR2 types, and it is well known that the maximum support length for the VARCHAR2 type is 4000. If you want to convert this column to a CLOB type due to business needs, it is not feasible to convert directly through the ALTER statement in Oracle. Here's how to convert a table column from a VARCHAR2 type to a CLOB type in an Oracle database, based on a specific case.

Sample Preparation

1. New two-sheet tb_without_data (this VARCHAR2 column contains no data) and Tb_with_data (this Varchar2 column contains data)

CREATE TABLE Tb_without_data (  ID number,  name VARCHAR2 (+),  description VARCHAR2 (2000));

CREATE TABLE Tb_with_data (  ID number,  name VARCHAR2 (+),  description VARCHAR2); INSERT INTO Tb_with _data values (1, ' David Louis ', ' He's capable of resolving such kind of issue '); insert into Tb_with_data VALUES (2, ' German Noemi ', ' She's very beatiful and charming '); insert into Tb_with_data VALUES (3, ' Oliver Queen ', ' He's main actor in the Gre En Arrow '); insert into Tb_with_data VALUES (4, ' Mark Williams ', ' He plays snooker very "); insert into Tb_with_data VALUE S (5, ' Sita Rama Raju Kata ', ' I do not know this guy '); Insert to Tb_with_data VALUES (6, ' Promethus ', ' This is a very nice m Ovie '); commit;

Error Method

ALTER TABLE tb_without_data MODIFY description Clob;

Error message:

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.

Workaroundmethod One: There is no data for this column can be modified by the following methods-first change the column to a long type, and then change to the CLOB type

ALTER TABLE Tb_without_data Modify description long;--first changed to long type ALTER TABLE Tb_without_data Modify Description clob;-- Change to CLOB type on the basis of long type

Note: For this column already exists data, cannot pass this method, otherwise will report the following error:

ALTER TABLE Tb_with_data Modify description long;--change columns that contain data

SQL Error:ora-01439:column to is modified must is empty to change datatype01439. 00000-  "column to be modified must is empty to change datatype"

method Two: This method is suitable for this column contains data and this column does not contain data two cases

Step One: Rename the column in the original table

ALTER TABLE tb_without_data Rename column description to description_bak;alter table tb_with_data Rename column Descriptio N to Description_bak;

Step Two: Add the column to the table and specify the column type to CLOB

ALTER TABLE Tb_without_data add description clob;alter Table tb_with_data add description clob;

Step three: The package data that contains data for this column is copied from the Step one renamed column (omitted for this step without data for this column)

Update Tb_with_data set description=description_bak;commit;

Step four: Remove the backup column from step one

ALTER TABLE tb_without_data drop column description_bak;alter table tb_with_data drop column Description_bak;

Step Five: Verify

1) Table Structure Verification

DESC tb_without_dataname        Null Type          ----------------------------ID               number        NAME             VARCHAR2 (100) DESCRIPTION      

DESC tb_with_dataname        Null Type          ----------------------------ID               number        NAME             VARCHAR2 (100) DESCRIPTION      CLOB  

2) Data validation

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 Char  Ming                        3 Oliver Queen               He's main actor in the Green Arrow                      4 Mark Williams              He plays snooker very well                               5 Sita Rama Raju Kata        I don't know this guy                                   6 Promethus The is                  a very nice movie                        

method Three: This method is suitable for this column contains data and this column does not contain data in two cases

Before explaining method three, the package table needs to be restored to the preparation stage, due to the time relationship, directly through the drop and then the re-create method, the script is as follows:

drop table Tb_without_data;drop Table Tb_with_data;create table Tb_without_data (  ID number,  name VARCHAR2 (100) ,  description VARCHAR2 (+)); Create table tb_with_data (  ID number,  name VARCHAR2,  description VARCHAR2); insert into Tb_with_data VALUES (1, ' David Louis ', ' He is capable of resolving such kind of issue '); Insert I Nto tb_with_data VALUES (2, ' German Noemi ', ' She's very beatiful and charming '); INSERT into tb_with_data values (3, ' Oliver Queen ', ' He's main actor in the Green Arrow '); insert into Tb_with_data VALUES (4, ' Mark Williams ', ' He plays snooker very we ll '); INSERT into tb_with_data values (5, ' Sita Rama Raju Kata ', ' I don't know this guy '); insert into tb_with_data values (6 , ' Promethus ', ' This was a very nice movie '); commit;

Step One: Rename two tables

Rename Tb_without_data to Tb_without_data_bak;rename tb_with_data to Tb_with_data_bak;

Step II: Create two new tables (create two tables with the following statement)

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;

Table structure and data validation:

Desc tb_without_dataname Null Type----------------------------ID number NAME VARCHAR2 (+) DESCRIPTION clobdesc tb_with_dataname Null Type---------------------------- ID number NAME VARCHAR2 (+) DESCRIPTION CLOB SELECT * from Tb_with_data;select * fr        Om Tb_with_data; ID NAME DESCRIPTION------------------------------------------ ------------------------------------------1 David Louis He is capable of resolving such kind of               Sue 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 was a very nice movie 6 rows selected  

Step three: Delete the backup table:

DROP table Tb_without_data_bak;drop table Tb_with_data_bak;

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

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

The table column in Oracle is changed from VARCHAR2 type to CLOB