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 ]
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.
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.
Table 10.4 summarizes the DBMS_RANDOM programs.
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.
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.
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.
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.
The TERMINATE procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.
SQL> exec DBMS_RANDOM.TERMINATE;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.