Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Changing precision of numeric column in Oracle

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.

like image 863
roymustang86 Avatar asked Feb 10 '12 19:02

roymustang86


People also ask

How do I change the precision of a column in Oracle?

By setting the scale, you decrease the precision. Try NUMBER(16,2). Can't you create a new table definition that duplicates the existing one except for the NUMBER(14.2) column and perform an "insert into newtable select ... from oldtable" - and when you are sure of success, delete the old and rename the new?

How do you increase the precision of a NUMBER in Oracle?

Most common way is to create a second column, copy the data, drop original column and rename former column. The other way is to set the original column to null and then you can use the alter table from above (see Stack Overflow for more details).

How do I change the precision of a column in SQL?

Just put decimal(precision, scale) , replacing the precision and scale with your desired values. I haven't done any testing with this with data in the table, but if you alter the precision, you would be subject to losing data if the new precision is lower.

What is precision in Oracle NUMBER format?

Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point. s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.


1 Answers

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; 
like image 68
Allan Avatar answered Sep 21 '22 18:09

Allan