DBMS_RANDOM: How to generate random numbers

DBMS_RANDOM has only been supplied with Oracle since version 8. If you are working with 6,7 etc you will have to come up with another solution of your own.

If DBMS_RANDOM hasn't been installed on your machine, then, as SYS, you should run the following scripts exactly in this order:

dbmsoctk.sql; prvtoctk.sql; dbmsrand.sql

(If you haven't got dbmsrand.sql somewhere it's a fair bet your version of Oracle doesn't support it.)

DBMS_RANDOM has four programs associated with it. These are: Initialize, Random, Seed, and Terminate.

Their usage is fairly straightforward.

Procedure DBMS_RANDOM.INITIALIZE takes a BINARY_INTEGER as a seed value. The more digits this value has, the more 'random' your random function will be. Five digits is the bare minimum and at least 8 are recommended for real randomness. You would call it as follows:

>exec DBMS_RANDOM.INITIALISE(9990199569);

Procedure DBMS_RANDOM.SEED allows you to change the initial seed value. Again, the supplied value should be comprised many digits for optimal randomness.

>exec DBMS_RANDOM.INITIALISE(797079758452);

Function DBMS_RANDOM.RANDOM returns a binary integer as a value. In order to reference it, you must previously have made a called to DBMS_RANDOM.INITIALIZE (q.v.) You would call it as follows

DECLARE
retval BINARY_INTEGER;
BEGIN
retval := DBMS_RANDOM.RANDOM;

Procedure DBMS_RANDOM.TERMINATE is used to release the memory consumed by DBMS_RANDOM. You should ensure that this is called once you are done with random number generation. You call it as follows:

>exec DBMS_RANDOM.TERMINATE;