CODEWITHSUNDEEP
sqloracle-databaseprecision
roymustang86
roymustang86

Reputation: 8553

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.

Upvotes: 48

Views: 135048

Answers (4)

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

I think the better way is to create a temp table and copy all data to it and change type then restore data:

create table EVAPP_FEES_temp as select * from EVAPP_FEES;
-- disable foreign keys to this table
delete EVAPP_FEES;
alter table EVAPP_FEES modify AMOUNT NUMBER(14,2);
insert into EVAPP_FEES select * from EVAPP_FEES_temp;
-- enable foreign keys to this table
drop table EVAPP_FEES_temp;

Upvotes: 0

DJ Dev J
DJ Dev J

Reputation: 41

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;

Upvotes: 1

Allan
Allan

Reputation: 17429

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;

Upvotes: 101

ron tornambe
ron tornambe

Reputation: 10780

By setting the scale, you decrease the precision. Try NUMBER(16,2).

Upvotes: 1

Related Questions