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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***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
0
Alex [***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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.
0
awking00Information Technology SpecialistCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.