Re: converting data types

Quixote wrote:
> 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

CREATE TABLE xyz AS
SELECT * FROM original_table

TRUNCATE TABLE original_table

ALTER TABLE original TABLE drop PRIMARY KEY

ALTER TABLE original_table MODIFY

ALTER TABLE original ADD CONSTRAINT

INSERT INTO original TABLE
SELECT * FRom xyz

DROP TABLE xyz

Is one way.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)