Changing the datatype of a column from VARCHAR2 to CLOB

Some days ago, I realized I hadn’t estimated correctly the size of a column of a table that receives data from an external source. I defined a VARCHAR2(4000 CHAR) datatype but it came a longer messageĀ  (I got an ORA-01461 error), so I decided to move the column to a CLOB datatype.

At first sight, it seems a simple matter. For example, if you have the following table definition:

   create table test_messages
   (message_id number             primary key,
    content   varchar2(4000 char) not null);

You would think to issue the following statement:

   alter table test_messages
   modify (content clob not null);

But, if you do that, you get an ORA-22296 error.

I had several options to solve the problem (please review the reference of this post), but I finally selected the following one:

   alter table test_messages
   add (temp_content clob);

   update test_messages
   set temp_content = content;

   alter table test_messages
   drop column content;

   alter table test_messages
   rename column temp_content to content;

   alter table test_messages
   modify (content not null);

Anyway, it’s very important to review all the application code affected by the change, because, for example, if you use the substr function, you have to change it to dbms_lob.substr. On the other hand, if you use JPA and you have an entity like:

   @Entity
   @Table(name = "TEST_MESSAGES")
   public class TestMessages implements Serializable {

       @Id
       @Column(name = "MESSAGE_ID")
       private Long messageId;

       @Column(name = "CONTENT", length = 4000, nullable = false)
       private String content;

       public Long getMessageId() {
           return messageId;
       }

       public void setMessageId(Long messageId) {
           this.messageId = messageId;
       }

       public String getContent() {
           return content;
       }

       public void setContent(String content) {
           this.content = content;
       }

   }

You have to change the definition of content to:

   @Lob
   @Column(name = "CONTENT", nullable = false)
   private String content;

Finally, I’d like to point out two issues that could affect to performance. The first one is the possibility of defer the removing of the initial column, running the following statement:

   alter table test_messages
   set unused column content;

instead of:

   alter table test_messages
   drop column content;

Later, in a period of low use of the database, you can issue:

   alter table test_messages
   drop unused columns;

The other thing regarding to performance is that it’s convenient reorganizing the storage of the table by running the following command:

   alter table test_messages move;

Statements like alter index <index_name> rebuild could also be needed.


References


SQL statement to get the weeks before the current date

I recently had to design a report to analyze the data of a week, so the user had to select that week using an input control. The data source of the combo box was the following SQL statement that runs in an Oracle Database:

select to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7) - 6, 
                    'mm/dd/yyyy')
  || ' - '
  || to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7), 
             'mm/dd/yyyy') week
from dual
  connect by level <= 52
order by level

This code employs the hierarchical query clause CONNECT BY to select the initial and the final day of the weeks before the current date, a period of a year more or less.