Oracle - Generate a Random number in Oracle between a interval

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);


Popular posts from this blog

Oracle - duplicate fields in RECORD,TABLE or argument list are not permitted

Oracle - ORA-29282: invalid file ID, ORA-06512: at "SYS.UTL_FILE"