Re: Random Number generator in Oracle???

From: René Guitar <rene.guitar_at_videotron.ca>
Date: Mon, 22 Jun 1998 20:43:01 GMT
Message-ID: <358EC17E.19848D5@videotron.ca>

Hi Keith! While surfing.. i found this code... maybe it can suit your need (I didn't tried it!) Hope this help!

REM 05-RAND.SQL
REM This file contains the code for the Random package in REM Chapter 5 of "Oracle PL/SQL Programming". It illustrates REM package initialization.

REM This is version 1.0 of this file, updated 2/18/96. REM Comments and questions should go to Scott Urman at REM surman_at_us.oracle.com.

CREATE OR REPLACE PACKAGE Random AS
/* Random number generator. Uses the same algorithm as the

   rand() function in C. */

• Used to change the seed. From a given seed, the same
• sequence of random numbers will be generated. PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);
• Returns a random integer between 1 and 32767. FUNCTION Rand RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(rand, WNDS );
• Same as Rand, but with a procedural interface. PROCEDURE GetRand(p_RandomNumber OUT NUMBER);
• Returns a random integer between 1 and p_MaxVal. FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(RandMax, WNDS);
• Same as RandMax, but with a procedural interface. PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER, p_MaxVal IN NUMBER); END Random; /

CREATE OR REPLACE PACKAGE BODY Random AS

  /* Used for calculating the next number. */   v_Multiplier CONSTANT NUMBER := 22695477;   v_Increment CONSTANT NUMBER := 1;

  /* Seed used to generate random sequence. */   v_Seed number := 1;

  PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS   BEGIN
    v_Seed := p_NewSeed;
  END ChangeSeed;

  FUNCTION Rand RETURN NUMBER IS
  BEGIN
    v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,

                  (2 ** 32));

    RETURN BITAND(v_Seed/(2 ** 16), 32767);   END Rand;

  PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS   BEGIN

• Simply call Rand and return the value. p_RandomNumber := Rand; END GetRand;

  FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS   BEGIN
    RETURN MOD(Rand, p_MaxVal) + 1;
  END RandMax;

  PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,

                       p_MaxVal IN NUMBER) IS

  BEGIN

• Simply call RandMax and return the value. p_RandomNumber := RandMax(p_MaxVal); END GetRandMax;

BEGIN
  /* Package initialization. Initialize the seed to the current

     time in seconds. */
  ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS'))); END Random;
/

eoswa wrote:

> Hi there,
> I have a requirement to produce a report select in a random selection of
> records grouped by area - i.e. A report with 2 randomly chosen property
> records for each of the 450 areas in the database. Does anyone have any
> ideas on the best way to do this. I've considered using the SYSDATE as a
> seed for the random number generator but can't figure out the best way to
> proceed.
> Any help would be greatly appreciated.
> Thanks
> Keith Hill
Received on Mon Jun 22 1998 - 15:43:01 CDT