48

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.

roymustang86
  • 8,054
  • 22
  • 70
  • 101

3 Answers3

98

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;
Bruno Medeiros
  • 2,251
  • 21
  • 34
Allan
  • 17,141
  • 4
  • 52
  • 69
  • 1
    what is the difference between these solutions? – BrownFurSeal Sep 03 '13 at 14:36
  • 6
    @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. – Allan Sep 04 '13 at 15:08
  • 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. – ADTC Nov 28 '14 at 06:53
  • 1
    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. – ADTC Nov 28 '14 at 07:05
  • @Allan 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? – Arun Gowda Dec 10 '20 at 07:11
  • 2
    @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. – Allan Dec 10 '20 at 15:38
  • 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. – dimplex Jul 02 '23 at 13:43
  • @dimplex: In Oracle, DDL automatically causes a commit, so there is an implicit commit when each `alter table` is executed. – Allan Jul 03 '23 at 03:37
1

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;
DJ Dev J
  • 41
  • 1
  • 3
1

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

ron tornambe
  • 10,452
  • 7
  • 33
  • 60
  • I tried every number from 1 to 38 and still get the same error. – roymustang86 Feb 10 '12 at 20:00
  • 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? – ron tornambe Feb 10 '12 at 20:09
  • 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 – roymustang86 Feb 10 '12 at 20:12
  • 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? You may need to drop and reinstate foreign keys, but it seems safe to me. – ron tornambe Feb 10 '12 at 20:24