Datatype cannot be changed in table

[arumurali]

I tried using the command alter table. But it is throwing the error as the datatype must be null to change the datatype. As i am trying to change the datatype from char(50) to NUMBER(2,0). As far as my searching in google, It is asking to drop the column and reinsert if yu need to change the datatype. It contains over 1000 datas. now i am really confused how to change the datatype to proceed further. Your idea or solution is highly appreciable.
Thanks in advance..!!!

[r035198x]

What do you want Oracle to do if you already have data in there that is not numbers and that is longer than 2 characters?

[arumurali]

No actually numbers are only present with only two numbers in each field. The data's are transferred from another source through a middle-ware Tuxedo. This type of imports mainly causes change in data type. So could u help me out of this?

[r035198x]

If you are allowed to alter the columns then chances are you are allowed to create and drop new ones as well.

So the safest approach is:
1.) Backup your data
2.) Create a new column (call it anything you want)
3.) Run an update copying from the old column to the new column
4.) Drop the old column.
5.) Create the new column with the right type.
6.) Run an update to copy from the column you created in 2.) above to the new correct column.
7.) Drop the column you created in 2.) above

[arumurali]

Yep not a bad idea but it has 100 rows with data's and it is so tedious to manually insert it again. So m searching for an alternative one, if no other option is left that's the only way left i guess :(

[r035198x]

Read the instructions I posted again. You don't have to run 100 statements. You'll only run a maximum of 6 statements no matter how many records there are in the database.

[arumurali]

Ohh Fine Fine,...Thanks for your valuable instructions r035198x..!!