## Random Numbers

Here's the situation, you need an evenly distributed sequence of random integers from 1 to 20. You decided to use the Oracle random number package 'dbmsrand'.

Incidentally, I decided to do it this way after searching Ask Tom.

There is a link to his web site to the right. Bookmark it.

While you're at it, bookmark Dan Morgan's page (link on the right).

http://www.psoug.org/reference/dbms_random.html

Ok, back to the story.

After you read the instructions on using it, you probably write something like this in your PL/SQL stored procedure:

AS
random_value number(2,0);
BEGIN
random_value := dbms_random.value(0,20);

Now you notice whoops, you're getting some 0s. You don't want 0. Plus you're not getting very many 20s. So you do this instead

random_value := dbms_random.value(1,21);

Much better! But whoops - you're getting the occasional 21! So you scratch your brain and do this:

random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;

That should pour the 21s into the 1 pile, which you noticed is very, very slightly lower than the others.

Now you think you're getting the values you want. So here are my questions:

1. Will this truly generate an even sample of numbers from 1 to 20? Will there be the right number of 1s and 20s?
2. Is there a better way?
3. Say you're generating a very large sequence, thinking about performance, do you see any problems with this approach I should consider?

Here is the complete solution:

--------------------
@utlraw
@prvtrawb.plb
@dbmsoctk
@prvtoctk.plb
@dbmsrand

create table value_holder as (f_value NUMBER(2,0));

create or replace procedure ValuePlacer (number_to_generate IN number)
AS
random_value number(2,0);
BEGIN
for x in 1..number_to_generate
LOOP
random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;
insert into value_holder values (random_value);
END LOOP;
END;

exec ValuePlacer(1000);

select f_value, count (f_value) from value_holder group by f_value;
-----------------

Thanks!

I'm not sure if I could follow your example, but it seems to me that what

SQL> select rownum from all_objects where rownum < 21 order by dbms_random.random;

ROWNUM
----------
19
10
11
15
9
1
14
16
12
7
2
8
4
6
17
13
5
3
20
18

HTH
Holger

Holger's solution is perfect for creating a random sequence. If your aim was to populate a table with randomly (and uniformly!) generated integers from 1 to 20:

SQL> select ceil(dbms_random.value(0,20)) rand_int from all_objects where rownum < 10000;

Yes, in theory this could produce a zero value if the randomly generated real number was exactly zero. However given that Oracle's precision is 38 digits the chance of that happening is pretty slim to say the least.

HTH,
Phil 