SOLVED: changing long col to varchar and vice versa

From: Schoen Volker <v.schoen_at_inplan.de>
Date: Fri, 17 Aug 2001 07:50:51 -0700
Message-ID: <F001.0036F042.20010817074555@fatcity.com>

Hi list,

I found a article at metalink which explanes how to change a long column to varchar. Example 2 worked for me.

Doc ID: Note 1022030.6

3. How do you move a LONG to varchar2?  

    It is not possible to change a long to a varchar useing the ALTER TABLE     command. You could create a new table with the varchar column, and     select the long into a PL/SQL variable, inserting that variable into     the varchar column in the new table.   

    Examples:

---

 

    (I) This example assumes the LONG column <=2000 characters:

---

         create or replace procedure 12vc is 
         cursor c0 is 
           select rowid, long_column 
           from table_name 
         begin 
         for c1 in c0 loop 
           update table_name 
           set varchar_column = c1.long_column 
           where rowid = c1.rowid; 
         end loop; 
         end; 
  
 

    (II) Another example that also performs string manipulation on the long:

---

 

         declare
         cursor my_cursor is
             select long_col 
             from   my_table; 
          my_var varchar2(32767); 
          begin 
            open my_cursor; 
            loop 
              fetch my_cursor into my_var; 
              exit when my_cursor%notfound; 
              my_var := substr(my_var,1,2000); 
              insert into new_table values (my_var); 
          end loop; 
          close my_cursor; 
          end; 
  
 
   (III) A method that uses export and import: 
         -------------------------------------  
 
         a) export the table with the long column 
         b) drop the table with the long column 
         c) import with the indexfile option (this writes the create table 
            statements in the specified file) 
         d) edit the indexfile and change the column datatype from long 
            to varchar 
         e) run the file as a script to create the table with the same name 
            but the datatype being changed from long to varchar2 
         f) import the data with ignore=y 
 

> Hi list,
>
> I want to change col-types in one table. I have table T1 with two columns
> COL1 LONG and COL2 VARCHAR2(2000). Now I want to change the col-types vice
> versa, that means COL1 should be VARCHAR2(2000) and COL2 should be LONG. I
> have a lot of data in that cols, but all data is smaller than 2000
> characters.
>
> Could someone tell how to convert these columns?
>
> TIA
>
> Volker Schön
> E-Mail: mailto:v.schoen_at_inplan.de
> http://www.inplan.de
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Schoen Volker
  INET: v.schoen_at_inplan.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 17 2001 - 09:50:51 CDT