I have a table where one of the column has datatype number(20). In one of the cases I need to save a value which is decimal into the column but the column automatically converts it into a whole number. Below is a simple test table I created to test this scenario.
create table test_tbl
(
amt number(20)
)
insert into test_tbl values (9.999)
The above inserts a value of 10 rather than 9.999. How can I save 9.999 exactly as it is?
Thanks
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