Automatic rounding off Number datatype while converted to VARCHAR2 issue
706307May 29 2013 — edited May 29 2013I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.
Can anyone please comment on it, how this problem can be fixed?
My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".
Illustration:
1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.
2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted
insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted
3) select * from test18;
COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296
4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter
select col1 || ',' || val as record_string
from test18;
RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296
Please notice, "First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.
Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place. Can anyone please throw off some light on it and provide the solution how the original record can be kept intact without rounding off.
Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296