Re: DROP or MODIFY COLUMN on Oracle 8 system

Alexander Mueller wrote:
> Hi
>
> I need to change the datatype of a column from VARCHAR2 to FLOAT
> preserving all data, which actually represent numeric data.
> I have to do it on an oracle8 enterprise system.
> Afaik
> ALTER TABLE tablename DROP COLUMN column
> and / or
> ALTER TABLE tablename MODIFY (column <NEW_DATATYPE>)
> doesn't work here, is that correct?
>
> So my migration plan is as follows:
> 1. create a temporary table with same schema as the src-table
> (except for the changed datatype of the one column)
> 2. insert / copy all old data into the new table
> problem: how will i keep values of auto-incremental fields?
> 3. Drop old table
> problem: how can i drop it, when its PK has references?
> 4. Re-create old table, with correct datatype for the one column
> 5. insert / copy data from tmp-table.
> problem: same as in 2.
> 6. drop temp-table
>
> Can anybody please help me on how to solve the problems mentioned. Or is
> there a better strategy (maybe built-in management tools?)
>
> Thanks,
> Alex

ALTER TABLE <table_name> ADD(new_column FLOAT(126));

UPDATE <table_name>
SET new_column = TO_NUMBER(old_column),

     old_column = NULL;

ALTER TABLE <table_name> MODIFY(old_column FLOAT(126));

UPDATE <table_name>
SET old_column = new_column,

ALTER TABLE <table_name> DROP new_column;

Is one way.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org