In Oracle, table columns are changed from VARCHAR2 type to CLOB type.

Source: Internet
Author: User
Tags sql error

In Oracle, table columns are changed from VARCHAR2 type to CLOB type.
Scenario

The columns in the original table are defined as the VARCHAR2 type. As we all know, the maximum length of the VARCHAR2 type is 4000. If you want to convert this column to the CLOB type because of business needs, it is not feasible to directly convert it using the ALTER statement in Oracle. The following describes how to convert a table column from the VARCHAR2 type to the CLOB type in the Oracle database.

Sample Preparation

1. Create two tables TB_WITHOUT_DATA (this VARCHAR2 column does not contain data) and TB_WITH_DATA (this Varchar2 column contains 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;
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.

Solution 1: If this column does not have data, you can change it to the Long type first and then to the clob type.
Alter table TB_WITHOUT_DATA modify description long; -- first, change it to the Long type alter table TB_WITHOUT_DATA modify description clob; -- change it to the clob type based on the Long type

Note: If this column already contains data, you cannot use this method. Otherwise, the following error is reported:

Alter table TB_WITH_DATA modify description long; -- change the column containing data
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"
Method 2: This method is applicable when this column contains data and this column does not contain data.

Step 1: 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 description to description_bak;

Step 2: add the column to the table and change the column type to clob

alter table TB_WITHOUT_DATA add description clob;alter table TB_WITH_DATA add description clob;
Step 3: copy the package data required for the data contained in this column from the one-name column in step (this step is omitted if this column does not have data)
update TB_WITH_DATA set description=description_bak;commit;
Step 4: Delete the backup column in step 1
alter table TB_WITHOUT_DATA drop column description_bak;alter table TB_WITH_DATA drop column description_bak;
Step 5: Verify

1) Table Structure Verification

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) data verification
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 
Method 3: This method is applicable when this column contains data and this column does not contain data.

Before method 3 is explained, you need to restore the table package to the preparation stage. Because of the time relationship, you can directly use the drop and 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(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;
Step 1: Rename two tables
rename TB_WITHOUT_DATA to TB_WITHOUT_DATA_BAK;rename TB_WITH_DATA to TB_WITH_DATA_BAK;
Step 2: create two new tables (use the following statement to create two tables)
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 verification:
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 
Step 3: delete the backup table:
DROP TABLE TB_WITHOUT_DATA_BAK;DROP TABLE TB_WITH_DATA_BAK;

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.