Re: PL-SQL Random Number Generator?

Guys,

nasof_at_hotmail.com wrote:
> Im sorry to butt in, BUT... I took exception with the reason that you cannot
> use a sequence is because it will wrap around at some point, but a random
> number wont.
> I understand the need for a random number (as long as you dont care about
> duplicates), but in Oracle a sequence can go as high as:
> 99,999,999,999,999,999,999,999,999,999,999,999,999 (type NUMBER(38)) THEN it
> will repeat. DAMN Thats a big number! If you started today and inserted a
> million rows a second (starting with a sequence number of 1) I bet it would
> take a loooooong time before it repeats! (any one care to figure this out?)

someone else wrote:
> > > this may be a pissant question, but why not use a sequence, and simply use
> > > ORACLE's random assignment from that sequence? Assign the sequence no
> > > maximum number, and don't choose the numbers in order..... I suppose this
> > > may be limiting, as far as security, or where you can access the number
> > > from, but for simply purposes, is this not a viable alternative?

Okay, I think there are some people out there who need to know what a SEQUENCE is.

In Oracle, a sequence is exactly what the name implies, a number assigned in a particular order, whether ascending or descending and whether skipping every odd, even or every n'th number. You cannot have a 'random sequence', it's a complete oxymoron.

You *could* use a sequence to seed a random number generator, but that doesn't help as the original poster is looking for such a generator in the first place.

Here are two random number generators from the Oracle Support Notes CD-ROM:

• random.sql --- create or replace package random is pragma restrict_references( random, WNDS, RNPS );

        procedure srand( new_seed in number );

        function rand return number;
        pragma restrict_references( rand, WNDS  );

        procedure get_rand( r OUT number );

        function rand_max( n IN number ) return number;
        pragma restrict_references( rand_max, WNDS);

        procedure get_rand_max( r OUT number, n IN number );

end random;
/

create or replace package body random
is

        multiplier      constant number         := 22695477;
        increment       constant number         := 1;
        "2^32"          constant number                 := 2 ** 32;
        "2^16"          constant number         := 2 ** 16;
        "0x7fff"        constant number         := 32767;
        Seed            number := 1;
--
        procedure srand( new_seed in number ) 
        is
        begin
                Seed := new_seed;
        end srand;
--
        function rand return number
        is
        begin
                seed := mod( multiplier * seed + increment, "2^32" );
                return bitand( seed/"2^16", "0x7fff" );
        end rand;
--
        procedure get_rand( r OUT number ) 
        is
        begin
                r := rand;
        end get_rand;
--
        function rand_max( n IN number ) return number 
        is
        begin
                return mod( rand, n ) + 1;
        end rand_max;
--
        procedure get_rand_max( r OUT number, n IN number ) 
        is
        begin
                r := rand_max( n );
        end get_rand_max;

--
begin
        select userenv( 'SESSIONID' ) into seed from dual;
end random;
/
--- end ---

--- rand.sql ---
package random is   
/* Linear congruential random number generator */   
   
/* Returns random integer between [0, r-1] */   
function rndint(r in number) return number;   
   
/* Returns random real between [0, 1] */   
function rndflt return number;   
   
end;   /* package random */   
 
package body random is   
/* Linear congruential random number generator */   
   
m constant number:=100000000;  /* initial conditions */   
m1 constant number:=10000;     /* (for best results) */   
b constant number:=31415821;   /*                    */   
   
a number;                      /* seed */   
   
the_date date;                 /*                             */   
days number;                   /* for generating initial seed */   
secs number;                   /*                             */   
   
/*-------------------------- mult ---------------------------*/   
/* Private utility function */   
   
function mult(p in number, q in number) return number is   
   p1 number;   
   p0 number;   
   q1 number;   
   q0 number;   
begin   
   p1:=trunc(p/m1);   
   p0:=mod(p,m1);   
   q1:=trunc(q/m1);   
   q0:=mod(q,m1);   
   return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m));   
end;   /* mult */   
   
/*-------------------------- rndint --------------------------*/   
/* Returns random integer between [0, r-1] */   
   
function rndint (r in number) return number is   
begin   
   /* generate a random number and set it to be the new seed */   
   a:=mod(mult(a,b)+1,m);   
   
   /* convert it to integer between [0, r-1] and return it */   
   return(trunc((trunc(a/m1)*r)/m1));   
end;   /* rndint */   
   
/*-------------------------- rndflt --------------------------*/   
/* Returns random real between [0, 1] */   
   
function rndflt return number is   
begin   
   /* generate a random number and set it to be the new seed */   
   a:=mod(mult(a,b)+1,m);   
   
   /* return it */   
   return(a/m);   
end;   /* rndflt */   
  
   
begin   /* package body random */   
   /* Generate an initial seed "a" based on system date */   
   /* (Must be connected to database.)                  */   
   the_date:=sysdate;   
   days:=to_number(to_char(the_date, 'J'));   
   secs:=to_number(to_char(the_date, 'SSSSS'));   
   a:=days*24*3600+secs;   
end;   /* package body random */   

--- end ---
--
Peter Moore
DBA, IS Ops, Sequent Computer Systems, Weybridge, UK
Email: ptmoore_at_sequent.com   Phone: +44 1932 814737