DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Modify table datatype from VARCHAR2(4000) to CLOB

  1. #1
    Join Date
    Nov 2005
    Posts
    10

    Modify table datatype from VARCHAR2(4000) to CLOB

    Gurus,

    I'm trying to modify a datatype from varchar2(4000) to Clob in one of my tables and I'm getting the following error:

    alter table gls_attr modify (attr_value CLOB);

    ORA-22858: invalid alteration of datatype

    I'm on 10G, shouldn't this work?

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    If the VARCHAR2(4000) column is empty, then you can modify the column to LONG first and then to CLOB.

    Code:
    SQL> desc test_clob
     Name                                 Null?    Type
     ------------------------------------ -------- -----------------
     A1                                            NUMBER
     A2                                            VARCHAR2(4000)
    
    SQL> alter table test_clob modify a2 long;
    
    Table altered.
    
    SQL> alter table test_clob modify a2 clob;
    
    Table altered.
    
    SQL> desc test_clob
     Name                                 Null?    Type
     ------------------------------------ -------- -----------------
     A1                                            NUMBER
     A2                                            CLOB

    If there is data in the column, you can do the following:


    Code:
    SQL> create table test_clob (a1 number, a2 varchar2(4000));
    
    Table created.
    
    SQL> insert into test_clob values (1, rpad('*',4000,'*'));
    
    1 row created.
    
    SQL> alter table test_clob add (a2_clob clob);
    
    Table altered.
    
    SQL> update test_clob set a2_clob = a2;
    
    1 row updated.
    
    SQL> alter table test_clob drop (a2);
    
    Table altered.
    
    SQL> alter table test_clob add (a2 clob);
    
    Table altered.
    
    SQL> update test_clob set a2 = a2_clob;
    
    1 row updated.
    
    SQL> alter table test_clob drop (a2_clob);
    
    Table altered.
    
    SQL> desc test_clob
     Name                                 Null?    Type
     ------------------------------------ -------- --------------
     A1                                            NUMBER
     A2                                            CLOB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width