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


10.3 DBMS_DDL: Compiling and Analyzing Objects		III. Server Management Packages