Increasing precision or scale of number column in Oracle

Question

I have a table named AAA with a column:

ED_RATE NOT NULL NUMBER(26,6) 

I want to increase its length using this statement:

ALTER TABLE ioa_invoice_line MODIFY FIXED_RATE NUMBER(26,15) 

Now upon executing this, I am getting an error, please advise how can I overcome this

ORA-01440: column to be modified must be empty to decrease precision or scale

1440. 00000 - column to be modified must be empty to decrease precision or scale

Answer 1

You can't decrease the precision of a numerical column in Oracle (Reference). What is interesting about your question is the old precision is NUMBER(26,6) and the new desired precision is NUMBER(26,15). This makes it appear, at first glance, that both columns would have the same 26 total digits of precision. This I believe may be a source of your confusion. However, the first case has 6 decimal places of precision while the second has 15 places. The 9 digits of precision being added to the column would presumably come at the cost of precision to the left of the decimal place. Hence, this could cause some of your data to be truncated.

If you want to add 9 digits of decimal precision, I believe you will have to also increase the total precision. Hence, the following should work:

ALTER TABLE ioa_invoice_line MODIFY FIXED_RATE NUMBER(35,15)
                                                      ^^ ^^ add 9 to both

Note that precision is limited to 38, with your need being within that limit.

Answer 2

You can rename it, then create new column, copy all values there and remove old column:

alter table ioa_invoice_line rename column ED_RATE to ED_RATE_TMP;
alter table ioa_invoice_line add ED_RATE NOT NULL NUMBER(26,15);
update ioa_invoice_line set ED_RATE=ED_RATE_TMP;
alter table ioa_invoice_line drop column ED_RATE_TMP;
execute utl_recomp.recomp_serial();

Another way is to move values into temporary colum, then increase length of ED_RATE and move values back:

alter table ioa_invoice_line add ED_RATE_TMP NUMBER(26,6);
alter table ioa_invoice_line modify column ED_RATE null;
update ioa_invoice_line set ED_RATE_TMP=ED_RATE, ED_RATE=null;
alter table ioa_invoice_line modify column ED_RATE NUMBER(26,15);
update ioa_invoice_line set ED_RATE=ED_RATE_TMP;
alter table ioa_invoice_line modify column ED_RATE not null;
alter table ioa_invoice_line drop column ED_RATE_TMP;
execute utl_recomp.recomp_serial();