0

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.

Shivam Kumar
  • 69
  • 1
  • 12

1 Answers1

2

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.

pifor
  • 7,419
  • 2
  • 8
  • 16
  • If I will write your command inside changeset then there will be several problems. You need to also take care of if this command will fail then how are you going to rollback. – Shivam Kumar Jun 19 '20 at 10:35
  • Please detail problems: database problems ? which error messages ? Liquibase problems ? If you have already tested this and this failed please edit your question with relevant ouput. – pifor Jun 19 '20 at 10:38
  • Can you tell me exactly what did you not understand? – Shivam Kumar Jun 19 '20 at 10:42
  • If you don't detail "problems" no one can really help you here. – pifor Jun 19 '20 at 10:43
  • Your solution will work but I am writing migration script in Liquibase and If your sql query fails then how will I rollback. If you don't understand then I can clarify again. – Shivam Kumar Jun 19 '20 at 10:51
  • 1
    There is nothing to rollback explicitely: in Oracle all DDL commands run in their own transaction: if an ALTER statement fails it will automatically be rolled back by Oracle. – pifor Jun 19 '20 at 10:55
  • Yes you are right But If I will add your command in my changeset at last. And suppose all the commands succeeded and yours one failed then your command will rollback but impact will be that In my database I will have one column with name `ADDRESS_CLOB` with `CLOB` data type which is result of previously succeeded commands. Which I don't want because I said that I want `ADDRESS` column in the end with `CLOB` data type. – Shivam Kumar Jun 19 '20 at 17:40