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
Use below approach.
ReplyDeletealter table modify long;
alter table modify clob;
Hi , I did the above steps still facing issue while inserting more than 4000 chars data to the column: Error starting at line : 5 in command -
ReplyDeleteINSERT INTO testing (test_datatype)
VALUES ('More than 4000 chars data here')
Error at Command Line : 6 Column : 9
Error report -
SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.