Re: DROP or MODIFY COLUMN on Oracle 8 system

> 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?

I'm not 100% sure. I do not have access to Oracle 8 any more and I cannot even find documentation for this version as it is so old. Any reason you can't upgrade to a newer version (9i or 10g)?

But that does not help you with your current problem....

> 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?

You can do steps 1 & 2 in one step as follows:

CREATE TABLE source_table_temp AS SELECT * FROM source_table;

Unlike MS Access or SQL Server, there is no column in Oracle that automatically increments on its own. This is done with a sequence and a trigger in Oracle.

> 3. Drop old table
> problem: how can i drop it, when its PK has references?

Before you drop the table, make sure you have everything you need to recreate it. You'll have to reverse-engineer the CREATE TABLE statement. Don't forget to reverse-engineer any CREATE INDEX and ALTER TABLE ADD CONSTRAINT statements for that table as well. Make sure you can recreate any triggers on that table. Those triggers might use a sequence, but dropping the table will not drop the sequence so you do not have to recreate the sequence.

If you have FK references to the table, you will want to disable those FK constraints before dropping the table:

ALTER TABLE referencing_table CONSTRAINT cons_name DISABLE;

> 4. Re-create old table, with correct datatype for the one column
> 5. insert / copy data from tmp-table.
> problem: same as in 2.

When changing from VARCHAR2 to a floating point number, it is possible that some character values will not convert to a number. So you might want to validate that your data will convert before you even begin this process. You might have data to clean up.

> 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?)

Don't forget to recreate indexes, constraints, and triggers on the new source table. Then drop the temp table.

If you were using Oracle 9i or 10g, you could do this operation online via the DBMS_REDEFINITION package without seriously impacting your production users. But then if your were using 9i or 10g, you could modify the column directly without dropping anything.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown