|
Chapter 10 Miscellaneous Packages |
|
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.
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.
|
Name |
Description |
Use in SQL |
|---|---|---|
|
INITIALIZE |
Initializes the random number generator with a seed value |
No |
|
Returns a random number |
No |
|
|
Resets the seed number used to generate the random number |
No |
|
|
Terminates the random number generator mechanism |
No |
DBMS_RANDOM does not declare any exceptions or nonprogram elements.
This section describes the DBMS_RANDOM programs in the order in which they are typically used.
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.
Here is an example of a call to initialize the DBMS_RANDOM package:
SQL> exec DBMS_RANDOM.INITIALIZE (309666789);
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);
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;
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 |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.