Oracle - Generate a Random number in Oracle between a interval

February 14, 2013

You can easily generate a random number by:

select dbms_random.normal from dual;

Unfortunately, Oracle does not provide a solution if you require a random number between a interval of values. To do it you might wand to do:

create or replace
function RANDOM_NUMBER(lower IN int,higher IN int)
return number
  return trunc(abs(( GREATEST(lower, higher) - LEAST(lower, higher) + 1 ) * abs(dbms_random.value)) + LEAST(lower, higher));

My own solution involved a last steep:

_select * _
from ( select RANDOM_NUMBER( (select min(id) from organisation), (select max(id) from organisation))
from dual);