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

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

Brian Peasland schreef:


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

Don't drop it, rename it. Do the same for the _TEMP table, and you're
done, iirc.
FK's, PK's and the like will be OK; PK index not

You can easily test this on a small table. Asktom has entries on this
subject - did you search?

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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org