Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> converting data types

converting data types

From: Quixote <yuya_at_isd.dp.ua>
Date: Thu, 12 Jan 2006 20:42:16 +0200
Message-ID: <dq67u5$2lbp$1@news.isd.dp.ua>


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 change 
datatype

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US