changing long col to varchar and vice versa
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).
Re: changing long col to varchar and vice versa
On Thu, 16 Aug 2001, [EMAIL PROTECTED] 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: [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).
Re: changing long col to varchar and vice versa
Export and Import David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Schoen Volker v.schoen@inpl To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] an.de cc: Sent by: Subject: changing long col to varchar and vice versa [EMAIL PROTECTED] om 08/16/2001 10:31 AM Please respond to ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
