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

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

  1. David says:

    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

    • logbuffer says:

      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!

  2. 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.

  3. 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

  4. A simpler version:

    SELECT 2*(round(dbms_random.value(1,100)/2,0)) even_random,
           2*(round(dbms_random.value(1,100)/2,0))+1 odd_random
    FROM dual
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: