Changing Precision of Numeric Column in Oracle

Changing precision of numeric column in Oracle

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;

PL/SQL instruction to get numeric column precision

You can find this information in user_tab_cols and all_tab_cols views:

create table tbl1_1(column_1 number(8,3));

select column_name, data_precision, data_scale 
  from user_tab_cols 
  where table_name = 'TBL1_1' and column_name = 'COLUMN_1';

---

COLUMN_NAME                    DATA_PRECISION DATA_SCALE
------------------------------ -------------- ----------
COLUMN_1                                    8          3

numeric precision in Oracle

The docs also state:

The FLOAT data type is a subtype of NUMBER

So it's a number under the covers and

To convert from binary to decimal precision, multiply n by 0.30103

Plugging the numbers in:

49 * 0.30103 = 14.75047
50 * 0.30103 = 15.05150
51 * 0.30103 = 15.65356

So float(50) and float(51) correspond to number(16), whereas float(49) is number(15)

You can verify this by taking a dump of the values:

create table numeric_types2 (
    f1 float(60), n1 number
);
insert into numeric_types2 
with rws as (
  select BIN_TO_NUM(
    1,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,1) n from dual
)
  select n, n from rws;

select dump ( f1 ), dump ( n1 ), 
       dump ( cast ( n1 as float(50) ) ) df50, 
       dump ( cast ( n1 as float(49) ) ) df49
from   numeric_types2;

DUMP(F1)                                 DUMP(N1)                                 DF50                                     DF49                                  
Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=8: 200,46,4,60,97,28,38,6    

Note that

dump ( f1 ) = dump ( n1 ) = dump ( cast ( n1 as float(50) ) )

Only casting the number as float(49) gives a different value.

Finally note that the docs also contain this recommendation:

Oracle FLOAT is available for you to use, but Oracle recommends that
you use the BINARY_FLOAT and BINARY_DOUBLE data types instead, as they
are more robust

Modifying an Oracle number column

ALTER TRIGGER trfoobar DISABLE;
ALTER TABLE foobar ADD (newcol number);
UPDATE foobar SET newcol=oldcol, oldcol=null;
ALTER TABLE foobar MODIFY(oldcol number(22,2));
UPDATE foobar SET oldcol=newcol;
ALTER TABLE foobar DROP(newcol);
ALTER TRIGGER trfoobar ENABLE;

Oracle Save decimal in a column with number datatype

In Oracle, you may (but are not required to) give both a precision and a scale to columns of NUMBER data type. NUMBER(20) is valid shorthand for NUMBER(20,0), meaning 20 digits, none after the decimal point.

How to fix it depends on your needs. Normally a NUMBER column should be declared simply as NUMBER. Specify precision and scale only if you have a good reason to.

For example: if your numbers must be limited to no more than 20 digits before the decimal point and no more than six decimal places (after the decimal point), the total PRECISION is 20 + 6 = 26. You would then declare NUMBER(26, 6).

You can change a column declared as NUMBER(20) to NUMBER (if that meets your needs otherwise), since there is no loss of information in this change. However, note that numbers that are ALREADY saved in the column have ALREADY LOST information, which cannot be recovered from the table; you will need to go to the original source of those numbers, if you need to fix it.

Brief demo (notice in the last output that the row inserted BEFORE the change has its value forever changed to 10, with no recovery possible):

create table test_tbl
(
  amt    number(20)
);

insert into test_tbl values (9.999);

select * from test_tbl;

AMT
---
 10

alter table test_tbl modify (amt number);

insert into test_tbl values (9.999);

select * from test_tbl;

  AMT
-----
   10
9.999

---

Related Topics