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:[EMAIL PROTECTED]
> http://www.inplan.de
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Schoen Volker
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
