# PL/SQL: Building an odd/even-random-number-generator

July 13, 2012 5 Comments

If you just need *any* integer random number, this would do the job:

select round(dbms_random.value(1,100),0) as random_number from dual;

The “dbms_random.value(1,100)” generates a random (float) number in the intervall 1 to 100. Actually the upper bound (here: 100) is not included, but as we round up the result to get an integer, we’ll still have it when rounding up something like 99,672517251 to 100.

Now, if we just want even random numbers (for what purpose ever), we’ll have to set up a loop that goes on generating random numbers until it spits out an even one. To check for “even” we use the modulo-function. Modulo divides a given number by a given divisor and just prints out the remainder. So if we divide a number by 2 and the remainder is 0 we know it’s an even number. Our loop looks like this:

begin declare n_randomvalue number default null; begin loop select round(dbms_random.value(1,100),0) into n_randomvalue from dual; exit when ( mod(n_randomvalue,2)=0 ); dbms_output.put_line('generated value is: ' || n_randomvalue); end loop; dbms_output.put_line('your random-value: ' || n_randomvalue); end; end;

To just get odd numbers we change the “mod(n_randomvalue,2)=0” to “mod(n_randomvalue,2)=1”.

Ok, now let’s make it a little more generic and put it in a function:

create or replace FUNCTION generateRandomNumber( v_oddeven VARCHAR2, n_start NUMBER, n_end NUMBER ) RETURN NUMBER AS n_randomvalue NUMBER DEFAULT NULL; n_modval NUMBER DEFAULT NULL; Begin -- check input-parameters: IF ( lower(v_oddeven) = 'odd' ) THEN n_modval := 1; Elsif ( Lower(V_Oddeven) = 'even' ) Then n_modval := 0; else raise_application_error (-20001, 'INVALID_ARGUMENT #1! VALID ARGUMENTS ARE: ODD, EVEN'); End If; If Not ( ( 0 <= N_Start ) And ( N_Start < N_End ) ) Then raise_application_error (-20002, 'INVALID_INTERVALL! INTERVALL-START MUST BE GREATER 0 AND LOWER THAN INTERVALL-END.'); End If; -- create random-number: LOOP SELECT ROUND(dbms_random.value(n_start,n_end),0) INTO n_randomvalue FROM dual; EXIT WHEN ( MOD(N_RANDOMVALUE,2)=N_MODVAL ); END LOOP; RETURN N_RANDOMVALUE; END;

…end test it:

SQL> select generateRandomNumber('even',1,100) from dual; GENERATERANDOMNUMBER('EVEN',1,100) ---------------------------------- 4 SQL> select generateRandomNumber('odd',1,100) from dual; GENERATERANDOMNUMBER('ODD',1,100) --------------------------------- 83 SQL>

Works. Now let’s check the “error-handling” too:

SQL> select generateRandomNumber('evenX',1,100) from dual; select generateRandomNumber('evenX',1,100) from dual * ERROR at line 1: ORA-20001: INVALID_ARGUMENT #1! VALID ARGUMENTS ARE: ODD, EVEN ORA-06512: at "TEST.GENERATERANDOMNUMBER", line 20 SQL> select generateRandomNumber('even',100, 1) from dual; select generateRandomNumber('even',100, 1) from dual * ERROR at line 1: ORA-20002: INVALID_INTERVALL! INTERVALL-START MUST BE GREATER 0 AND LOWER THAN INTERVALL-END. ORA-06512: at "TEST.GENERATERANDOMNUMBER", line 23 SQL>

…as expected.

For more info on generating random-values with PL/SQL see here:

PL/SQL: Generate random values

*Edit 2012-11-06:*

Thanks to commentator David we now know a smarter, better performing way:

To get an odd random-number in the range 1-99 we can achieve this with just one single call to DBMS_RANDOM with this calculation:

select round(dbms_random.value(0,49),0)*2+1 as random_number from dual;

And to get even random-numbers in the range 2-100 we can use this:

select round(dbms_random.value(1,50),0)*2 as random_number from dual;

This particular example is a little silly. If you really want an odd random number R from 1 to 99, generate a random number, round it to a temporary int T in the range 0 to 49, then calculate R = 2*T+1. But this “keep trying a random number until we get one that is suitable” process is nice for picking random points on a disk or on the surface of a sphere or other situations.

http://mathworld.wolfram.com/SpherePointPicking.html

http://rosettacode.org/wiki/Constrained_random_points_on_a_circle

I don’t like your tone in calling my efforts “silly”. Suffer from Asperger syndrome, eh?

But anyhow…. you’re right: your approach is smarter. Thanks for that hint!

I think, all you need to do is to generate a random whole number between 0 and 4. The next step is to turn it into odd/even digit (times 2 and optionally + 1) and replace the last digit of the random number you obtain the “regular” way.

SELECT ROUND(DBMS_RANDOM.VALUE(1,100),-1) + 2*ROUND(DBMS_RANDOM.VALUE(0,4),0) even_random,

ROUND(DBMS_RANDOM.VALUE(1,100),-1) + 2*ROUND(DBMS_RANDOM.VALUE(0,4),0)+1 odd_random

FROM dual

A simpler version: