Changing precision of numeric column in Oracle

Question

Currently I have a column that is declared as a NUMBER  I want to change the precision of the column to NUMBER 14 2     SO  I ran the command   alter table EVAPP FEES modify AMOUNT NUMBER 14 2     for which  I got an error        column to be modified must be empty to decrease precision or scale   I am guessing it wants the column to be empty while it changes the precision and I don t know why it says we want to decrease it while we are increasing it  the data in the columns can t be lost  Is there a short workaround for this  I don t want to copy it into another table and drop it afterwards  or rename a column and copy in between columns  because there is a risk of losing data between the transfers and drops

User · Answer

By setting the scale  you decrease the precision  Try NUMBER 16 2

User · Answer

If the table is compressed this will work   alter table EVAPP FEES add AMOUNT TEMP NUMBER 14 2    update EVAPP FEES set AMOUNT TEMP   AMOUNT   update EVAPP FEES set AMOUNT   null   alter table EVAPP FEES modify AMOUNT NUMBER 14 2    update EVAPP FEES set AMOUNT   AMOUNT TEMP   alter table EVAPP FEES move nocompress   alter table EVAPP FEES drop column AMOUNT TEMP   alter table EVAPP FEES compress

User · Answer

Assuming that you didn t set a precision initially  it s assumed to be the maximum  38   You re reducing the precision because you re changing it from 38 to 14   The easiest way to handle this is to rename the column  copy the data over  then drop the original column   alter table EVAPP FEES rename column AMOUNT to AMOUNT OLD   alter table EVAPP FEES add AMOUNT NUMBER 14 2    update EVAPP FEES set AMOUNT   AMOUNT OLD   alter table EVAPP FEES drop column AMOUNT OLD    If you really want to retain the column ordering  you can move the data twice instead   alter table EVAPP FEES add AMOUNT TEMP NUMBER 14 2    update EVAPP FEES set AMOUNT TEMP   AMOUNT   update EVAPP FEES set AMOUNT   null   alter table EVAPP FEES modify AMOUNT NUMBER 14 2    update EVAPP FEES set AMOUNT   AMOUNT TEMP   alter table EVAPP FEES drop column AMOUNT TEMP

[sql] Changing precision of numeric column in Oracle

Examples related to sql

Examples related to oracle

Examples related to precision