DBA Tips Archive for Oracle

  


Creating Random Numbers in PL/SQL

by Jeff Hunter, Sr. Database Administrator

Overview

The following article presents two methods for creating random numbers using PL/SQL.

Method 1 is a PL/SQL implementation of the linear congruential method of generating random numbers. It is in the form of a PL/SQL package, so it should be easy add to existing applications.

Method 2 takes the seconds past midnight and converts it to a number between 0 to 32767. Although this method requires less coding, Method 1 has two advantages:

  1. Method 2 does not produce evenly distributed results. If you map 86400 possible numbers to 32768 possible final values, some values become more likely than others

  2. Method 2 is not repeatable. However, given the same seed, Method 1 produces the same sequence of numbers
If even distribution and repeatability are not important in your application, you can use Method 2 with no problems.

Method 1

Linear congruential random number generator
CREATE OR REPLACE PACKAGE random IS

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

CREATE OR REPLACE PACKAGE BODY random IS

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

  -- ------------------------
  -- 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;

  -- ---------------------------------------
  -- 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;
 
  -- ----------------------------------
  -- 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(a/m);
    END;

BEGIN
  -- Generate initial seed "a" based on system date
  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;
/

Method 2

Produce random # between 0 to 32767 using seconds past midnight
SELECT
  TRUNC( 
    (TO_NUMBER(SUBSTR(TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'sssss'))/86399),-7,7))/10000000)*32767
  ) random 
FROM dual;


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 11-Mar-2003 00:00:00 EST
Page Count: 35848