Re: changing long col to varchar and vice versa

On Thu, 16 Aug 2001, v.schoen_at_inplan.de wrote:

> 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.

You can't do this unless the data in the column you are modifying is null or the resultant datatype is bigger than the datatype being changed.

Here's an attempt to show this. It has the syntax you are looking for at least.

,----
| SQL> SQL> create table tmp_modify (fld1 long, fld2 varchar2(2000));
|
| Table created.
|
| SQL> insert into tmp_modify values ('AA','BB');
|
| 1 row created.
|
| SQL> alter table tmp_modify modify fld1 varchar2(2000);
| alter table tmp_modify modify fld1 varchar2(2000)
| *
| ERROR at line 1:
| ORA-01439: column to be modified must be empty to change datatype
|
| SQL> alter table tmp_modify modify fld2 long;
| alter table tmp_modify modify fld2 long
| *
| ERROR at line 1:
| ORA-01754: a table may contain only one column of type LONG
`----

This shows the above statement.

I did the following as well.

,----
| SQL> delete tmp_modify;
|
| 1 row deleted.
|
| SQL> alter table tmp_modify modify fld1 varchar2(2000);
|
| Table altered.
|
| SQL> insert into tmp_modify values ('AA','BB');
|
| 1 row created.
|
| SQL> alter table tmp_modify modify fld2 long;
| alter table tmp_modify modify fld2 long
| *
| ERROR at line 1:
| ORA-01439: column to be modified must be empty to change datatype
`----

Hm... I would have thought that I could have modified the varchar2 to long even with data in that one.

The documentation on this can be found here http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/statem2a.htm#2055629

Option 1
You can just create a view which switches the names of the columns for you?

Option2
You could create a table of the three columns, two columns you need to modify along with the key. Insert those two columns into it. Update the columns of original table to null. Modify the long to varchar2(2000) and then the other to long. Update the columns with the values saved in temporary table.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Galen Boyer
  INET: galenboyer_at_hotpop.com

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).