- Get link
- X
- Other Apps
- Get link
- X
- Other Apps
At the beginning of creation database structure you decided to use datatype VARCHAR2 and that time you thought 4000B should be enough. But later you want to insert more than 4000B, it's time to change this datatype to CLOB.
If you don't have data in the table, it's ideal situation and the solution is very easy:
SQL> create table test ( 2 id number, 3 name varchar2(50), 4 comments varchar2(4000) 5 ); Table created. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS VARCHAR2(4000) Because there is not direct way, how to modify column from VARCHAR2 to CLOB, we need to modify column in two steps. Steps: - VARCHAR2 --> LONG - LONG --> CLOB SQL> alter table test modify comments LONG; Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS LONG SQL> alter table test modify comments CLOB; Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS CLOB SQL> insert into test (id, name, comments) values (1,'name_1','comment_1'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME COMMENTS ---------- --------------- ------------------------------ 1 name_1 comment_1 |
Not very ideal situation is, if you don't have empty table, in this case is not possible to modify column from VARCHAR2 to CLOB so easy:
SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS VARCHAR2(4000) SQL> begin for i in 1 .. 100 loop insert into test ( id, name, comments ) values ( i, 'name_'||i, 'comments_'||i ); end loop; commit; end; / PL/SQL procedure successfully completed. SQL> select count(*) from test; COUNT(*) ---------- 100 As before, there isn't possible to modify directly from VARCHAR2 to CLOB: SQL> alter table test modify comments CLOB; alter table test modify comments CLOB * ERROR at line 1: ORA-22858: invalid alteration of datatype And even more, there isn't possible to change VARCHAR2 to LONG: SQL> alter table test modify comments LONG; alter table test modify comments LONG * ERROR at line 1: ORA-01439: column to be modified must be empty to change datatype The whole process will be: - create new column with datatype CLOB - update this column - drop old column with database VARCHAR2 - rename created column to the original column name SQL> alter table test add (comments_tmp clob); Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS VARCHAR2(4000) COMMENTS_TMP CLOB SQL> update test set comments_tmp=comments; 100 rows updated. SQL> commit; Commit complete. SQL> select * from test where rownum<5; ID NAME COMMENTS COMMENTS_TMP ---------- --------------- ------------------------------ --------------------------- 1 name_1 comments_1 comments_1 2 name_2 comments_2 comments_2 3 name_3 comments_3 comments_3 4 name_4 comments_4 comments_4 SQL> alter table test drop column comments; Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS_TMP CLOB SQL> select * from test where rownum<5; ID NAME COMMENTS_TMP ---------- --------------- ------------------------------ 90 name_90 comments_90 92 name_92 comments_92 93 name_93 comments_93 94 name_94 comments_94 SQL> alter table test rename column comments_tmp to comments; Table altered. SQL> desc test Name Null? Type ---------------------------------- -------- ------------------------------------ ID NUMBER NAME VARCHAR2(50) COMMENTS CLOB SQL> select * from test where rownum<5; ID NAME COMMENTS ---------- --------------- ------------------------------ 90 name_90 comments_90 92 name_92 comments_92 93 name_93 comments_93 94 name_94 comments_94 |
Comments
Solution is nice and easy, but it doesn't solve the column order problem. If the application is hardcoded, and it is referencing or using column order, this solution won't work once you are not modifying the last column. Is it possible to use this solution and prevent column order change?
ReplyDeleteHello Jan,
Deletei don't agree, experienced programmer don't care about the order of columns in the table, in the application shouldn't be used "select * from" or "... order by 2", all the time the programmer should use names of columns or aliases.
But there is also possibility to leave the original order of the columns:
1. create another table with the order of columns which is suitable for you
2. disable constraints on the original table
3. copy all data from the original table to the newly created table
4. drop the original table
5. rename the newly created table to the original name
6. enable all constraints
I hope it's helpful for you.
Regards,
Lucia