Re: How to Randomly Generate a Value

From: Lee levy <levy.lee.ls_at_bhp.com.au>
Date: 1996/01/14
Message-ID: <4dc27e$no_at_gossamer.itmel.bhp.com.au>#1/1

In article <4d9kot$4bf_at_news2.delphi.com>, DAGMARA_at_news.delphi.com (DAGMARA_at_DELPHI.COM) says:
>
>I have a client who wishes to randomly generate a number using SQL. I
>don't know of any ORACLE function that will do this; but perhaps this
>may be a new or unsupported function someone knows about? How are others
>doing this?
>
>Dagmar Anne Bogan
>
>Kopania & Komorovsky Specializing in Data Architecture
>Richardson, TX 75081 and Performance Engineering
>
>dagmara_at_delphi.com
>

I kept the following clipping in case I never needed it: (( cant guarantee that it works)

In article <4689c8$chu$2_at_mhafn.production.compuserve.com> Steve Edelstein <74160.645_at_CompuServe.COM> writes:
>Does anyone have or know of a random number generator written in
>or compatible with PL/SQL? Replies appreciated...

I saw that someone else had posted the PL/SQL code that they had typed in. Here's the code as I originally created it (and presumably as Oracle support is distributing it)...

--Russ

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

-----End Package Spec-----

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

-----End Package Body-----

------/\/\------+---------------------------------------------------
     / / /\     |  Lee Levy, ISSD Systems Development, Del Code (34)
    / / /  \    |  BHP Information Technology,  ACN 006 476 213
   / / / /\ \   |  PO Box 261, Warrawong, NSW 2502, Australia

   \ \/ / / / | PH: +61 42 75-5485 Fax: -5500 Tie: 8855-     \ / / / | Internet : levy.lee.ls_at_bhp.com.au

-----\/\/\/-----+---------------------------------------------------

Opinions expressed are mostly my own, so give me some credit. Received on Sun Jan 14 1996 - 00:00:00 CET