Ahti Kitsik
Building tools with Python, Java, Go, JS. For enquiries email inbox@ahtik.com

writings biography

Changing number scale for Oracle NUMBER columns

03 Jun 2009 by @ahtik

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!