rem    Package RND: Generate random numbers
rem
rem    This package should be installed as SYS.  It generates a sequence of
rem    random 38-digit Oracle numbers.  The expected length of the sequence
rem    is about power(10,28), which is hopefully long enough.  The generator
rem    used is called Delayed Fibonacci: m(i) := result = m(i)+m(i+24 mod 55).
rem
rem    Numbers can be fetched through SQL statements, for example
rem
rem      insert into a values (rnd.val);
rem      select rnd.val from dual;
rem      variable x number;
rem      execute :x := rnd.val;
rem      update a set a2=a2+1 where a1 < :x;
rem
rem    When no seed is provided, the package is originally initialized 
rem    with the current user name and the time down to the current second.
rem    If this package is seeded twice with the same seed, then accessed
rem    in the same way, it will produce the same results in both cases.


drop public synonym rnd
/
drop package rnd
/

create package rnd is

  -- initialize the generator using a user-supplied string as the seed
  procedure  init (seed in varchar2);
  pragma restrict_references (init, WNDS);
  
  -- get a random Oracle number x, 0.0 <= x < 1.0
  function   val return number;
  pragma restrict_references (val, WNDS);

  -- get a random Oracle number x, LOW <= x < HIGH
  function   val (low in number, high in number) return number;
  pragma restrict_references (val, WNDS);

  -- get a random number from a normal distribution
  function norm return number;
  pragma restrict_references (norm, WNDS);

  -- produce a random string
  function STRING (opt char, len number)
          /* "opt" specifies that the returned string may contain:
             'u','U'  :  upper case alpha characters only
             'l','L'  :  lower case alpha characters only
             'a','A'  :  alpha characters only (mixed case)
             'x','X'  :  any alpha-numeric characters (upper)
             'p','P'  :  any printable characters
          */
     return varchar2;  -- string of <len> characters (max 60)
  pragma restrict_references (STRING, WNDS);

  type num_array is table of number index by binary_integer;
end;
/

create package body rnd is
  mem        num_array;            -- big internal state hidden from the user
  counter    binary_integer;       -- counter through the results
  other      binary_integer;
  saved_norm number;               -- unused random normally distributed value

  -- initialize the generator with any string
  procedure init (seed in varchar2) is
    junk     varchar2(2000);
    piece    varchar2(20);
    randval  number;
    j        binary_integer;
  begin
    saved_norm := null;
    counter    := 0;
    other      := 24;
    junk       := to_single_byte(seed);
    for i in 0..54 loop
      piece   := substr(junk,1,19);
      randval := 0;
      j       := 1;
      for j in 1..19 loop
        randval := 1e2*randval + nvl(ascii(substr(piece,j,1)),0.0);
      end loop;

      randval := randval*1e-38 + i*.01020304050607080910111213141516171819;
      mem(i)  := mod( randval, 1.0 );
      junk    := substr(junk,20);
    end loop;

    for j in 0..10 loop
      for i in 0..54 loop
        randval := mem(mod(i+55-1, 55)) * 1e24;
        randval := mod( randval, 1.0) + trunc(randval)*1e-38;
        randval := mem(i)+randval;
        if (randval >= 1.0) then
          randval := randval - 1.0;
        end if;
        mem(i) := randval;
      end loop;
    end loop;
  end init;

  -- give values to the user
  function val return number is
    randval  number;
  begin
    counter := counter + 1;
    if counter >= 55 then
      counter := 0;
      for i in 0..30 loop
        randval := mem(i+24) + mem(i);
        if (randval >= 1.0) then
          randval := randval - 1.0;
        end if;
        mem(i) := randval;
      end loop;
      for i in 31..54 loop
        randval := mem(i-31) + mem(i);
        if (randval >= 1.0) then
          randval := randval - 1.0;
        end if;
        mem(i) := randval;
      end loop;
    end if;
    return mem(counter);
  end val;

  -- give values in a certain range
  function val ( low in number, high in number) return number is
  begin
    return (val*(high-low))+low;
  end val;

  -- Random numbers in a normal distribution.
  -- Pilfered from Knuth volume 2.
  function norm
    return number is  -- 38 decimal places: Mean 0, Variance 1
    v1  number;
    v2  number;
    r2  number;
    fac number;
  begin
    if saved_norm is not null then    -- saved from last time
      v1 := saved_norm;              -- to be returned this time
      saved_norm := null;
    else
      r2 := 2;
      while r2 > 1 or r2 = 0 loop   -- Find two independent uniform variables
        v1 := 2*val-1;
        v2 := 2*val-1;
        r2 := v1*v1 + v2*v2;        -- r2 is radius, that is, distance from 0
      end loop;      -- 0 < r2 <= 1:  in unit circle
      /* Now derive two independent normally-distributed variables */
      fac := sqrt(-2*ln(r2)/r2);
      v1 := v1*fac;         -- to be returned this time
      saved_norm := v2*fac; -- to be saved for next time
    end if;
    return v1;
  end  norm;

  function STRING (opt char, len number)
    return varchar2 is  -- string of <len> characters
    optx char (1)      := lower(opt);
    lo   number;
    rng  number;
    n    number;
    xstr varchar2 (60) := null;
  begin
    if    optx = 'u' then       -- upper case alpha characters only
      lo := 65; rng := 26;      -- ASCII 41 to 5A (hex)
    elsif optx = 'l' then       -- lower case alpha characters only
      lo := 97; rng := 26;      -- ASCII 61 to 7A (hex)
    elsif optx = 'a' then       -- alpha characters only (mixed case)
      lo := 65; rng := 52;      -- ASCII 41 to 5A and 61 to 7A (see below)
    elsif optx = 'x' then       -- any alpha-numeric characters (upper)
      lo := 48; rng := 36;      -- ASCII 30 to 39 and 41 to 5A (see below)
    elsif optx = 'p' then       -- any printable characters
      lo := 32; rng := 95;      -- ASCII 20 to 7E (hex)
    else
      lo := 65; rng := 26;   -- default to upper case
    end if;

    for i in 1 .. least(len,60)
    loop
      /* Get random ASCII character value in specified range */
      n := lo + trunc(rng * val); -- between lo and (lo + rng -1)

      /* Adjust for split range */
      if    optx = 'A' and n > 90 then
        n := n+6;       -- exclude ASCII characters 5B to 60
      elsif optx = 'X' and n > 57 then
        n := n+7;       -- exclude ASCII characters 3A to 40
      end if;

      xstr := xstr||chr(n);     -- Append character to string
    end loop;

    return xstr;

  end STRING;

begin
  init(to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')||user);
end;
/

create public synonym rnd for rnd
/
grant execute on rnd to public
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips