- Get link
- Other Apps
- Get link
- 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