[Chapter 10] 10.4 DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)

10.4 DBMS_RANDOM: Generating Random Numbers (Oracle8 Only)

The DBMS_RANDOM package provides a built-in random number generator utility. Oracle Corporation suggests that this package will run faster than generators written in PL/SQL itself because DBMS_RANDOM calls Oracle's internal random number generator.

Oracle describes this package as a relatively simple interface for a random number generator, limited to returning an 8-digit number. They recommend that you use the DBMS_CRYPTO_TOOLKIT package if you need a more sophisticated engine with more options. This package is available with Trusted Oracle.[ 3 ]

[3] I must point out that DBMS_RANDOM is built on top of DBMS_CRYPTO_TOOLKIT, which is not documented in this book.

As with any random number generator, before you can obtain any random numbers from DBMS_RANDOM, you must first initialize the package by providing a seed number with DBMS_RANDOM's INITIALIZE procedure. You can later reseed the random number generator via RANDOM_SEED. When you need a random number, issue a call to the RANDOM, which returns a random number for your use. Finally, when you no longer need to use the random number generator, terminate DBMS_RANDOM via the TERMINATE procedure.

10.4.1 Getting Started with DBMS_RANDOM

The DBMS_RANDOM package is created when the Oracle database is first installed. The dbmsrand.sql script found in the built-in packages source code directory (described in Chapter 1 ) contains the source code for this package's specification. This script is called by catoctk.sql , which contains the scripts needed to use the PL/SQL Cryptographic Toolkit Interface. The scripts create the public synonym DBMS_RANDOM for the package and grant EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

NOTE: If you are running Oracle8 Release 8.0.3, DBMS_RANDOM may not have been installed in your database. In this case, you need to execute the following scripts in the specified order from within your SYS account: dbmsoctk.sql , prvtoctk.plb, and finally dbmsrand.sql (it contains both the package specification and body for DBMS_RANDOM).

Table 10.4 summarizes the DBMS_RANDOM programs.

Table 10.4: DBMS_RANDOM Programs
Name	Description	Use in SQL
INITIALIZE	Initializes the random number generator with a seed value	No
RANDOM	Returns a random number	No
SEED	Resets the seed number used to generate the random number	No
TERMINATE	Terminates the random number generator mechanism	No

DBMS_RANDOM does not declare any exceptions or nonprogram elements.

10.4.2 DBMS_RANDOM Interface

This section describes the DBMS_RANDOM programs in the order in which they are typically used.

10.4.2.1 The DBMS_RANDOM. INITIALIZE procedure

Before you can use the DBMS_RANDOM package, you must initialize it with this program,

PROCEDURE DBMS_RANDOM.INITIALIZE (seed IN BINARY_INTEGER);

where seed is the seed number used in the algorithm to generate a random number. You should provide a number with at least five digits to ensure that the value returned by the DBMS_RANDOM.RANDOM function will be sufficiently, well, random.

The INITIALIZE procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.

10.4.2.1.1 Example

Here is an example of a call to initialize the DBMS_RANDOM package:

SQL> exec DBMS_RANDOM.INITIALIZE (309666789);

10.4.2.2 The DBMS_RANDOM. SEED procedure (Oracle8 only)

Once the random number generator has been initialized, you can change the seed value used by DBMS_RANDOM with the SEED procedure. The specification is,

PROCEDURE DBMS_RANDOM.SEED(seed IN BINARY_INTEGER);

where seed is the seed number used in the algorithm to generate a random number. As with INITIALIZE, you should provide a number with at least five digits to ensure that the value returned by the DBMS_RANDOM.RANDOM function will be sufficiently random.

The SEED procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.

10.4.2.2.1 Example

Here is an example of a call to reseed the DBMS_RANDOM package:

SQL> exec DBMS_RANDOM.SEED (455663349);

10.4.2.3 The DBMS_RANDOM. RANDOM function (Oracle8 only)

Call the RANDOM function to retrieve a random number.

FUNCTION DBMS_RANDOM.RANDOM RETURN BINARY_INTEGER;

The RANDOM runction does not assert a purity level with the RESTRICT_REFERENCES pragma.

10.4.2.3.1 Example

Here is an example of a call to RANDOM to obtain a random number:

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

10.4.2.4 The DBMS_RANDOM.TERMINATE procedure (Oracle8 only)

When you are done with DBMS_RANDOM, you should terminate the program. This will release any memory used by the package.

PROCEDURE DBMS_RANDOM.TERMINATE;

The TERMINATE procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.

Here is an example of a call to terminate the DBMS_RANDOM package:

SQL> exec DBMS_RANDOM.TERMINATE;