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

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