Ahti Kitsik
Building develper tools. Python, Java, JavaScript.

writings about @ahtik

Changing number scale for Oracle NUMBER columns

By Ahti Kitsik, 03 Jun 2009

Whenever you try to change your NUMBER column to more specific scale like NUMBER(10,2) you end up with an error message:

SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale

There is a way out of this but it is a bit hacky. Let me know if there is a more elegant solution!
Following example also takes care of the situation when your column is not nullable.

alter table MYTABLE add AMOUNT_TEMP NUMBER;
update MYTABLE set AMOUNT_TEMP = AMOUNT;
alter table MYTABLE modify AMOUNT NULL;
update MYTABLE set AMOUNT=null;
alter table MYTABLE modify AMOUNT number(10,2);
update MYTABLE set AMOUNT=AMOUNT_TEMP;
alter table MYTABLE modify AMOUNT NOT NULL;
alter table MYTABLE drop column AMOUNT_TEMP;

Take care!

@ahtik is on twitter!