Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

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;