5 DBMS_RANDOM

The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM is not intended for cryptography.

This chapter contains the following topics:

• Using DBMS_RANDOM

  • Operational notes

• Summary of DBMS_RANDOM subprograms

---

Using DBMS_RANDOM

• Operational notes

---

Operational notes

• DBMS_RANDOM.RANDOM produces integers in [-2^^31, 2^^31).

• DBMS_RANDOM.VALUE produces numbers in [0,1) with 38 digits of precision.

DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling one of the overloads of DBMS_RANDOM.SEED. To produce different output for every run, simply omit the call to "Seed" and the system will choose a suitable seed for you.

---

Summary of DBMS_RANDOM subprograms

Table 5-1 DBMS_RANDOM package subprograms

Subprogram	Description
INITIALIZE procedure	Initializes the package with a seed value.
NORMAL function	Returns random numbers in a normal distribution.
RANDOM function	Generates a random number.
SEED procedure	Resets the seed.
STRING function	Gets a random string.
TERMINATE procedure	Terminates package.
VALUE function	This function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal point (38-digit precision), while the overloaded function gets a random Oracle number x, where x is greater than or equal to low and less than high.

Note:

The INITIALIZE procedure, RANDOM function and the TERMINATE procedure are all obsolete and, while currently supported, are included in this release for legacy reasons only.

Note:

Notes on data types:

• The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses "BINARY_INTEGER" to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

• The INTEGER and NUMBER(38) data types are also identical. This document uses "INTEGER" throughout.

---

INITIALIZE procedure

This procedure initializes the generator (but see the usage notes).

Syntax

DBMS_RANDOM.INITIALIZE (
   val  IN  BINARY_INTEGER);

Pragmas

PRAGMA restrict_references (initialize, WNDS);

Parameters

Table 5-2 INITIALIZE procedure parameters

Parameter	Description
val	The seed number used to generate a random number.

Usage notes

This procedure is obsolete as it simply calls the SEED procedure.

---

NORMAL function

This function returns random numbers in a standard normal distribution.

Syntax

DBMS_RANDOM.NORMAL
  RETURN NUMBER;

Pragmas

PRAGMA restrict_references (normal, WNDS);

Return value

Table 5-3 NORMAL return value

Parameter	Description
number	A random number.

---

RANDOM function

This procedure generates and returns a random number.

Syntax

DBMS_RANDOM.RANDOM
   RETURN binary_integer;

Pragmas

PRAGMA restrict_references (random, WNDS);

Return value

Table 5-4 RANDOM return value

Parameter	Description
binary_integer	A random integer greater or equal to -power(2,31) and less than power(2,31).

---

SEED procedure

This procedure resets the seed.

Syntax

DBMS_RANDOM.SEED (
   val  IN  BINARY_INTEGER);

DBMS_RANDOM.SEED (
   val  IN  VARCHAR2);

Pragmas

PRAGMA restrict_references (seed, WNDS);

Parameters

Table 5-5 SEED procedure parameters

Parameter	Description
val	Seed number or string used to generate a random number.

Usage notes

The seed can be a string up to length 2000.

---

STRING function

This function generates and returns a random string.

Syntax

DBMS_RANDOM.STRING
   opt  IN  CHAR,
   len  IN  NUMBER)
  RETURN VARCHAR2;

Pragmas

PRAGMA restrict_references (string, WNDS);

Parameters

Table 5-6 STRING function parameters

Parameter	Description
opt	Specifies what the returning string looks like: 'u', 'U' - returning string in uppercase alpha characters. 'l', 'L' - returning string in lowercase alpha characters. 'a', 'A' - returning string in mixed case alpha characters. 'x', 'X' - returning string in uppercase alpha-numeric characters. 'p', 'P' - returning string in any printable characters. Otherwise the returning string is in uppercase alpha characters.
len	The length of the returning string.

Return value

Table 5-7 STRING function return value

Parameter	Description
VARCHAR2	A VARCHAR2 value.

---

TERMINATE procedure

When you are finished with the package, call the TERMINATE procedure (but see Usage notes)

Syntax

DBMS_RANDOM.TERMINATE;

Usage notes

This procedure performs no function and, although it is currently supported, it is obsolete and should not be used.

---

VALUE function

The basic function gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). Alternatively, you can get a random Oracle number x, where x is greater than or equal to low and less than high.

Syntax

DBMS_RANDOM.VALUE
  RETURN NUMBER;

DBMS_RANDOM.VALUE(
  low  IN  NUMBER,
  high IN  NUMBER)
RETURN NUMBER;

Parameters

Table 5-8 VALUE function parameters

Parameter	Description
low	The lowest number in a range from which to generate a random number. The number generated may be equal to low.
high	The highest number below which to generate a random number. The number generated will be less than high.

Return value

Table 5-9 VALUE function return value

Parameter	Description
NUMBER	An Oracle NUMBER value.