How change Column Data Type from Number to Varchar 2 with out deleting data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How change Column Data Type from Number to Varchar 2 with out deleting data

Hybrid View

  1. #1
    Join Date
    Feb 2002
    Posts
    14

    How change Column Data Type from Number to Varchar 2 with out deleting data

    Hi,

    I landed into a funny problem. One of my table DEPARTMENTS as dep_id as number, recentny my organization started to use characters for department ids. Could you tell me is there any way i can change the data type to character with out emptying the column.

    Thanks in advance
    Jagannadh

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This might work ...

    Code:
    alter table
       departments
    add (dep_id_chr varchar2(30));
    
    update
       departments
    set
       dep_id_chr=to_char(dep_id,'fm99999999999999');
    
    commit;
    
    alter table
       departments
    drop (dep_id);
    
    alter table
       departments
    rename column dep_id_chr to dep_id;
    you'd probably have to drop any indexes on dep_id and rebuild them afterwards, and you'd have to revalidate any views, pl/sql etc referencing the table.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2008
    Posts
    2
    ...but it's going to be terribly slow in case of a hugely populated table (millions records). It will take hours to finish.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    was there a need to bring up up a 4 and a half year old thread just to say that?

  5. #5
    Join Date
    Jan 2008
    Posts
    2

    Smile

    nice hit! you'll be surprised but it was a very 1st link in google search.
    I think, for now, probably, the best solution is to use create as select constuct, don't you think?

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