Re: Random Number Generator SEED

From: Russ Seligman <rseligma_at_oracle.com>
Date: 1995/08/29
Message-ID: <RSELIGMA.95Aug29154002_at_seinfeld.oracle.com>#1/1

In article <41vnr4$koi_at_darwin.nbnet.nb.ca> godbouts_at_gov.nb.ca (Stephane C. Godbout) writes:
>After having determined that Oracle does not have a random number generator, I
>programed one. The generator (after being given a seed) will provide a number
>between 0 and 1.
>
>The seed has to be between 1 and 2,147,483,647. I was thinking of using the
>milliseconds stamp off of the computer however, It seems that Oracle doesn't
>have a function to do this.

Oracle does provide the sysdate function, which returns the current time. However, you may want to use the random number generation package that I've posted to this group from time to time.

At the end of this note is a PL/SQL package that implements a linear congruential random number generator. It can exist either stored in a database or locally in a client application (such as Developer/2000). You can call it using the following syntax:

  x := random.rndint(10); -- returns an integer between 0 and 9 inclusive

  x := random.rndflt; -- returns floating point between 0 and 1 inclusive  

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Russ Seligman                 "If there's nothing wrong with me, maybe
Oracle Corporation             there's something wrong with the universe."
rseligma_at_us.oracle.com           --Beverly Crusher, ST:TNG

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-  

• the package spec: **********

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 */     

• the package body: **********

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 */   Received on Tue Aug 29 1995 - 00:00:00 CEST