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

In Oracle, table columns are changed from VARCHAR2 to CLOB and varchar2clob.
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!

How Does oracle Change the varchar2 column to clob?

In the recent migration work, the column data type of some tables is too small. The following is an example:
SQL> create table t_varchar2_to_clob (a varchar2 (100); the table has been created. SQL> insert into t_varchar2_to_clob values ('zxy'); 1 row has been created. SQL> commit; submitted completely. -- Tested: the null or real columns of varchar2 cannot be changed to the clob type.
SQL> alter table t_varchar2_to_clob modify a clob;
Alter table t_varchar2_to_clob modify a clob * row 1st error:
ORA-22858: Invalid changes to data types-the following uses the transition method
SQL> create table t_varchar2_to_clob (a varchar2 (10); the table has been created. SQL> insert into t_varchar2_to_clob values ('zxy'); 1 row has been created. SQL> insert into t_varchar2_to_clob values ('zxy1'); 1 row has been created. SQL> insert into t_varchar2_to_clob values ('zxy12'); 1 row has been created. SQL> insert into t_varchar2_to_clob values ('zxy123 '); 1 row has been created. SQL> commit; submitted completely. SQL> select * from t_varchar2_to_clob; A ---------- zxyzxy1zxy12zxy123 -- Add A new column
SQL> alter table t_varchar2_to_clob add B clob; the table has been changed. SQL> col a for a10
SQL> col B for a10SQL> r1 * select * from t_varchar2_to_clobA B
---------- Zxyzxy1zxy12zxy123 -- migrate data from the source column to the new column
SQL> update t_varchar2_to_clob set B = a; four rows have been updated. SQL> commit; submitted completely. -- Check whether data in the source and new columns is consistent
SQL> select a, B from t_varchar2_to_clob; A B
---------- Zxy zxyzxy1 zxy1zxy12 zxy12
Zxy123 zxy123 -- delete the source column
SQL> alter table t_varchar2_to_clob drop column a; the table has been changed. -- Rename the new column as the source column
SQL> alter table t_varchar2_to_clob rename column B to a; the table has been changed. -- View the modified table
SQL> desc t_varchar2_to_clob;
Is the name empty? Type
---------------------------------------------------------------------------
--------------------
A clob Summary: 1. the above operations apply to development ...... the remaining full text>

How Does oracle Change column attributes? I want to change the CLOB type to the varchar2 (20) type. The error statements are as follows:

The statement is correct. Thanks, but there may be some things that limit the conversion of CLOB and ROWID types.