Alter VARCHAR2 Column To CLOB


We can not change type of a column from VARCHAR2 to CLOB
but we can achieve this result in following steps:

1. Add a new CLOB column
2. UPDATE CLOB column = VARCHAR2 column
3. DROP VARCHAR2 column
4. Rename CLOB column to VARCHAR2 column name


EXAMPLE:
create table myemp(empid varchar2(100), notes varchar2(4000));

insert into myemp values (1,'This is xxx');
insert into myemp values (2,'This is yyy');
insert into myemp values (3,'This is zzz');
commit;

alter table myemp add notes_new clob;

update myemp set notes_new=notes;
commit;

alter table myemp drop column notes;

alter table myemp rename column notes_new to notes;


Related Posts
- Append String to CLOB in Optimized way
- ORA-01489: result of string concatenation is too long
- PLS-00172: string literal too long
- Alter VARCHAR2 Column To CLOB

1 comment:

  1. Use below approach.
    alter table modify long;
    alter table modify clob;

    ReplyDelete