Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> converting data types
Hello All,
I am looking for a best way of modifying the column data types. I use the Oracle 9.i
For example I have the table:
CREATE TABLE testtable (
column1 Float(126) NOT NULL CONSTRAINT TESTTABLE_COLUMN1_Primary
PRIMARY KEY,
column2 NUMBER(15) NOT NULL);
I have the column1 with type 'DOUBLE PRECISION' [Float(126)]
and I want to perform it's conversion to any other type (for ex. to VARCHAR2
type), and this column is not empty.
If I will convert it just by
ALTER TABLE testtable MODIFY(column1 VARCHAR2(24)) I will receive the next ORA message: Error: ORA-01439: column to be modified must be empty to changedatatype
I can try to convert it in the following way - by creating the temporary column, dropping the old one and renaming the temp column:
ALTER TABLE testtable ADD (tmp000 VARCHAR2(24)); UPDATE testtable SET tmp000 = TO_CHAR(column1); ALTER TABLE TESTTABLE DROP (column1); ALTER TABLE testtable RENAME COLUMN tmp000 TO column1;
But if the column1 is a primary key, or the table contains indexes and foreign keys on this column, than I will receive an error message. Maybe exists any other nice workaround or I should always check for indexes and keys, drop them and recreate again?
Your quick answer will be very appreciated.
Thank you,
Yuriy Yakovlev
Received on Thu Jan 12 2006 - 12:42:16 CST