CODEWITHSUNDEEP
oracle-databaseoracle10g
Andromeda
Andromeda

Reputation: 12897

Changing data type in oracle without deleting the data

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

Upvotes: 2

Views: 8183

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

  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.

Upvotes: 5

Florin Ghita
Florin Ghita

Reputation: 17643

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.

Upvotes: 3

Related Questions