Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Converting CLOB to VARCHAR2

    So, we want to convert some columns from CLOB to VARCHAR2. Can I just do something like the following, or will some CLOB data get lost if I don't use something from DBMS_LOB, or something, to convert the data. As a critical note, the data in the CLOB column doesn't exceed 800 characters, and the VARCHAR2 we're creating holds 2000:

    Code:
     add the "soon-to-be" DIRECTIONS column, naming it something novel
    ALTER TABLE PUBEDUC.EDUDRECT 
     ADD (DIRECTIONS_NEW  VARCHAR2(2000));
    
     move the data from the CLOB column into the VARCHAR2 column 
    UDPATE PUBEDUC.EDUDRECT 
    SET DIRECTIONS_NEW = DIRECTIONS;
    
     remove the CLOB column
    ALTER TABLE PUBEDUC.EDUDRECT DROP COLUMN DIRECTIONS;
    
     rename the VARCHAR2 column back to the original field name
    ALTER TABLE PUBEDUC.EDUDRECT 
    RENAME COLUMN DIRECTIONS_NEW TO DIRECTIONS;
    ---=cf

  2. #2
    Join Date
    May 2006
    Posts
    132
    Yes, that will work.

Posting Permissions

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