Creating Random Numbers in PL/SQL

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;