What is the largest length of a NUMBER that Oracle will "support"?
The documentation says the following:
Datatype Limits indicates:
"Can be represented to full 38-digit precision"
NUMBER Data Types indicates:
"Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point."
I realize if I define a column as simply NUMBER, I can insert numbers with a size up to 126 digits. However, Oracle seems to only maintain the first 40 digits IN MOST cases. The most number of digits it seems to allow is 40 before it starts to replace with 0's.
With numbers that have more than 40 digits, Oracle will sometimes replace any numbers after the 38th digit with a 0 and other times will replace with 0 after the 39th or 40th digit.
Therefore, what is the most number of digits we can have confidence in Oracle storing safely?
This was the code I used for testing this process.
create table max_num (num number);
declare
l_x number;
begin
for x in 1..200
loop
l_x := x;
insert into max_num values (rpad(1, x, 1));
end loop;
exception
when others then
dbms_output.put_line('STOP: '||l_x);
dbms_output.put_line(sqlerrm);
end;
/
select num, length(replace(num, 0)) from max_num;