Problem in converting varchar2 column to clob data type in Oracle database

Question

I have one Person table in my database. I am using Oracle database. I have one column ADDRESS which has varchar2 datatype. I want to change datatype of this column from varchar2 to clob. I am doing this change by Liquibase. Here is my change set.

<changeSet id="15" author="shivam">
    <sql>
        ALTER TABLE PERSON ADD ADDRESS_CLOB VARCHAR2(2040 CHAR);
        ALTER TABLE PERSON MODIFY ADDRESS_CLOB LONG;
        ALTER TABLE PERSON MODIFY ADDRESS_CLOB CLOB;
        UPDATE PERSON SET ADDRESS_CLOB = ADDRESS;
        ALTER TABLE PERSON DROP COLUMN ADDRESS;
    </sql>
    <rollback>
        ALTER TABLE PERSON DROP COLUMN ADDRESS_CLOB;
    </rollback>
</changeSet>

By above changeset I will be able to convert from varchar2 to clob. But as you can see that my final column name is ADDRESS_CLOB. But my requirement is final column name should be ADDRESS and it should be of clob datatype. How can I do that please help me.

Answer 1

You just need to rename the column with:

alter table person rename column address_clob to address;

Note that in Oracle Database DDL is not transactional like in other databases.