Changing data type in oracle without deleting the

Question

Product Version Oracle 10g

I have a table in oracle with some data in it. There is a column which of type Number(10,0). Now i want to change it to Number(10,2) to allow decimals. But to change, oracle is not allowing without deleting the existing data. I know i can take the data backup -> delete data -> change type -> insert data back. But the problem is there is an auto insert column which gets value from a sequence. So when i insert it back those id will change....I am not sure that if anyone is using that id as a key to get the data. So how can i change it without altering the ids..

Please help..

Answer 1

1. Add the new NUMBER(12,2) column, but with a different name.
2. Update the new column, converting all the values from the old column.
3. Drop the old column.
4. Rename the new column with the desired name.

Answer 2

Probably you want number(12,2) to keep precision. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

Alternately, you may disable the trigger / do the work / enable the trigger.