ORA-22858: changes to data types are invalid varchar2 type conversion to clob type

In Oracle, if a column type is varchar2, it cannot be directly converted to the clob type. It can be modified indirectly. The following is an experiment:

SQL> create table test (id int, name varchar2 (10 ));

Tablecreated

SQL> insert into test values (1, 'A ');

1row inserted

SQL> insert into test values (2, 'B ');

1row inserted

SQL> commit;

Commitcomplete

SQL> select * from test;

ID NAME

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

1

2 B

SQL> desc test

NameType Nullable Default Comments

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

ID INTEGER Y

NAMEVARCHAR2 (10) Y

 

SQL> alter table test modify (name clob );

Altertable test modify (name clob)

ORA-22858: Invalid change to Data Type

If conversion is performed directly, an error is returned!

 

We can perform the following operations indirectly:

Create a clob column, insert the value of the original column into the new column, delete the original column, and rename the new column.

 

SQL> alter table test add name1clob;

Table altered

SQL> update test setname1 = name;

2 rows updated

SQL> select * from test;

 

IDNAME NAME1

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

1

2 B

 

SQL> desc test

Name Type Nullable Default Comments

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

ID INTEGER Y

NAME VARCHAR2 (10) Y

NAME1CLOB Y

 

SQL> commit;

Commit complete

SQL> alter table test dropcolumn name;

Table altered

SQL> alter table test renamecolumn name1 to name;

Table altered

SQL> select * from test;

ID NAME

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

1

2 B

SQL> desc test

NameType Nullable Default Comments

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

ID INTEGER Y

NAMECLOB Y

GoldenGate performs two-way Oracle-Oracle replication without using a Data Pump

One-way Oracle-Oracle Replication Using the GoldenGate Data Pump

How to debug Oracle Data Pump (expdp/impdp)

Oracle Database Export data pump (EXPDP) file storage location

Export of Oracle 10g Data Pump Partition Table