AhtiK Ventures

Changing number scale for Oracle NUMBER columns

Filed under: Development Practices, Tools and Frameworks

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.

[sql]
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;
[/sql]

Take care!

Comments (1) 6:27 pm