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

2 comments:

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

    ReplyDelete
  2. 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 -
    INSERT 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.

    ReplyDelete