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.

Terrazas

Answer 1

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;

Scaly

what is the difference between these solutions? – Largess

@BrownFurSeal: The first solution will move the changed column, so that it becomes the last column in the table. The second solution will preserve the original order of the columns. – Scaly

The first method destroys the column (which can cause problems if the column is used somewhere). The second method is non-destructive but requires the column to be nullable. If it's non-nullable, you will need to add commands to make it nullable, then restore it to be non-nullable after the change. – Squally

And if the column is a primary key column, you cannot do second method because primary key columns are enforced as non-nullable by their nature. Basically, you're screwed. – Squally

@Scaly After restoring the old column, the new column values seem to be rounded off to nearest value with HALF_UP logic.. Ex: 0.125 is changed to 0.13 after changing scale from 3 to 2. Is there a way we can control the rounding off logic? – Wellesley

@ArunGowda: In one of the steps where you're setting the values, you could use the TRUNC or CEIL or FLOOR functions to force non-standard rounding. I.E. update EVAPP_FEES set AMOUNT_TEMP = TRUNC(AMOUNT, 2); to always round down. – Scaly

Don't you need a commit after the update? You may lose data by dropping the source otherwise. Please, correct me if I'm wrong. – Bacchius

@dimplex: In Oracle, DDL automatically causes a commit, so there is an implicit commit when each alter table is executed. – Scaly

Answer 2

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

Jukebox

I tried every number from 1 to 38 and still get the same error. – Terrazas

Sorry I misread the post. When you define a column with NUMBER, it assigns the maximum value of 38, therefore any scale will decrease the precision with the possibly of data being lost. Why do you think you may lose some data by creating a new table and copying data from the old? – Jukebox

There are thousands of records, and in case one of the update/alter statement fails, there may not be a way to roll it back – Terrazas

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? You may need to drop and reinstate foreign keys, but it seems safe to me. – Jukebox

Answer 3

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;

Vitiate

Answer 4

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;

Sosthenna