Changing the number precision in Oracle [duplicate

Question

This question already has an answer here:

• Changing precision of numeric column in Oracle 3 answers

I haven't seen this exact issue... I apologize if I missed it.

I have a database I inherited from the company that created it when they lost the contract to my company. Now, the customer has an "urgent" issue to change the precision of a value. They want to change a number from 10.3 to 10.8; the field is defined in the database as 10,3. In my research of the issue, It seems I have to do quite a bit of manipulation to do this since the table needs to be empty to change the precision. I am assuming that even though the default is 10.38 (or whatever it is), since they defined it as 10,3 upon creation, if I just increase the field in the ColdFusion code, when it saves, it will truncate to 10,3? The change would involve me capturing the current data, deleting the data, changing the precision, then reloading the data, ensuring the existing data has been changed as well. Yes, I know that's not in detail, but hopefully gets the point across. Thank you.

Answer 1

Do you really want to change from a NUMBER(10,3) to a NUMBER(10,8)? That would significantly restrict the range of numbers that could be stored in the field - which is precisely why you can't do it when there is data in the column.

Or do you mean that you want to increase the number of decimal places from 3 to 8, while still allowing the same overall range of values? If so, then I think you want to change NUMBER(10,3) to NUMBER(15,8) - and you should be able to do that using a simple ALTER even if the column contains data.

Answer 2

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;

OR

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;