I want to generate random Numbers in Oracle 11g. But due to client request, I cannot use dbms_random.random of Oracle.
Is there any other way I can generate random numbers?
Duplication are strictly not allowed, numbers should be of length 4
0001
0053
9871
0098
0003
If any other Oracle library from which I can generate, then suggestions would be appreciated :)
If you need to select a four digit number without duplication, you can start by creating a table of numbers from 0 to 9999:
create table numbers as
select level - 1 as seqnum
from dual
connect by level <= 10000;
Then order this randomly and choose however many non-repeating numbers you want. Up to 10,000 of course. I think this will work:
with random as (
select to_char(n.seqnum, '0000') as val, row_number() over (order by sys_guid) as seqnum
from numbers
)
select *
from random
where seqnum <= 10;
If you do not want to use dbms_random and your number must be distinct you can use pure sequence with a little help of "Fermat's little theorem".
See: http://en.wikipedia.org/wiki/Fermat%27s_little_theorem
Simply use sequence number, put into the formula and you get "semi-random" unique number. Search wikipedia about "Discrete logarithm". The same principle is also used for OTP (one time passwords), if the sequence number is decreasing.
u can use milliseconds from SYSTIMESTAMP
select to_char(systimestamp,'ff')/1000 from dual
dbms_randomrandom(). That does not seem like a reasonable limitation.