I'd stick with varchar2
MatteoP, July 21, 2016 - 12:52 pm UTC
At first glance I was for 2 number columns solution, but it becomes tricky when you filter for a range that makes both columns change.
As an example let's assume that datatype limit is 50 but we need to store numbers up to 99; We need a table with a column for units and a column for tens
create table mat_test_tens
(tens number(1), unit number(1));
insert into mat_test_tens
select trunc(level/10), mod(level, 10) from dual connect by level < 100;
If I want records between 19 and 31 I need write a weird query that need to be coded dynamically
select * from mat_test_tens
where case when tens = 1 and unit >= 9 then 1
when tens = 2 then 1
when tens = 3 and unit <= 1 then 1
else 0
end = 1;
So I would stick with a zero left padded number stored as varchar2
July 21, 2016 - 2:25 pm UTC
Good point. Your plans for querying and using the values may force your decision.
Are you sure NUMBER won't meet your needs?
Brian Camire, July 21, 2016 - 7:07 pm UTC
The NUMBER data type can store up to 38 digits of decimal precision, allowing for integers up to 10**38 - 1 (or about 2**126). Is that enough to meet your needs?
July 22, 2016 - 7:27 am UTC
Presumably not, but only the OP can answer that!
keys and/or data?
Chuck Jolley, July 21, 2016 - 8:18 pm UTC
Another advantage of the varchar2 idea if these are data in addition to keys is that there are lots of libraries of math functions for manipulating enormous numbers represented by strings.
Also, as a general rule storage is cheap and complexity is expensive.