is it possible to change datatype table in oracle

Hi ,

i wan to change my table column
varchar2 to numeric

but column have some value ?

How can i alter this ?
LVL 20
Sathish David  Kumar NArchitectAsked:
Who is Participating?
Alexander Eßer [Alex140181]Software DeveloperCommented:
Another way:
0 - enable parallel processing upon your original table is possible
1 - create table temp_table as select [column list], to_number(col_vchar_2be_num) [nologging] from your_table;
2 - drop your_table
3- alter table temp_table rename to your_table
4 - add constraints, indexes, trigger, PKs, FKs, whatsoever to your newly built table

OR -if this has to be done "online"- use DBMS_REDEFINITION
Alexander Eßer [Alex140181]Software DeveloperCommented:
One way could be:
1 - Create the new column at the end of the table.

2 - Run an update to populate the new table column

3 - Drop the old table column

4 - Re-name the new column to the original column name
Gerwin Jansen, EE MVETopic Advisor Commented:
Besides to how you would do this (see above), I'd first check all values whether they can be converted to numeric, in the 'migration', decide what you do with strings that cannot be converted. For strings that cannot be converted, you could decide not set a value in the new column and empty the varchar2 column when the conversion succeeds. That would leave you with maybe just a few rows to fix manually.
Mark GeerlingsDatabase AdministratorCommented:
Oracle does not directly support changing the datatype of a column, if the column contains data.  So, your options are:
1. Export the data from the table, truncate the table, change the column datatype, then import the data you exported.
2. Create a new (empty) table with the structure you want, copy the data to the new table, copy all triggers, grants and constraints to the new table, drop the original table, then rename the new table to the original name.
3. Use DBMS_REDEFINITION to do the steps in #2 for you.
4. Add a column, copy the data from your varchar2 column to the new column, and set the value of your varchar2 column to null in the same update, then change your varchar2 column to number, then copy the data back into this column, and set the new column to null in the same update, then drop the new column.  But, this approach can leave you with some "chained rows" that will add a performance penalty for queries of this table until you get a chance to re-organize this table.  (That can be as simple as: "alter table [table_name] move [tablespace_name]; followed by: "alter index [index_name] rebuild;" for each index on the table.)

Before you try any of these, query the data in the column to make sure that there are no rows that have non-numeric data in them,  If there are any, fix these manually first.

Which of these options is your best option?  That is for you to decide.
Similar to what has been posted with slight variation -
alter table t add column(numcol number);
update table t set numcol = to_number(oldcol);
update table t set oldcol = null;
alter table t modify (oldcol number);
update table t set oldcol = numcol;
alter table drop numcol.
This allows you retain the columns in their original order. Note- updating using to_number() requires that the varchar2 values are, in fact, numeric or require additional manipulation to replace non-numeric values first.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.