6 DBMS_RANDOM

The DBMS_RANDOM package provides a built-in random number generator.

This chapter contains the following topics:

Using DBMS_RANDOM
- Operational notes
Summary of DBMS_RANDOM subprograms

Note:

DBMS_RANDOM is not intended for cryptography.

Using DBMS_RANDOM

Operational notes

Operational notes

The RANDOM function produces integers in the range [-2^^31, 2^^31).
The VALUE function produces numbers in the range [0,1) with 38 digits of precision.

DBMS_RANDOM can be explicitly initialized but does not require initialization before a call to the random number generator. It automatically initializes 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 produces the same result 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 an overload of SEED. To produce different output for every run, simply omit the seed call. Then the system chooses a suitable seed for you.

Summary of DBMS_RANDOM subprograms

Table 6-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	One version 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). The other version gets a random Oracle Database number `x`, where `x` is greater than or equal to a specified lower limit and less than a specified higher limit.

Note:

The INITIALIZE procedure, RANDOM function and TERMINATE procedure are deprecated. They are included in this release for legacy reasons only.

Notes:

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 is deprecated. Although currently supported, it should not be used. It initializes the random number generator.

Syntax

DBMS_RANDOM.INITIALIZE (
   val  IN  BINARY_INTEGER);

Parameters

Table 6-2 INITIALIZE procedure parameters

Parameter	Description
`val`	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;

Return value

The random number, a NUMBER value

RANDOM function

This procedure is deprecated. Although currently supported, it should not be used. It generates and returns a random number.

Syntax

DBMS_RANDOM.RANDOM
   RETURN binary_integer;

Return value

A random BINARY_INTEGER value greater than or equal to -power(2,31) and less than power(2,31)

Usage notes

See the NORMAL function and the VALUE function.

SEED procedure

This procedure resets the seed used in generating a random number.

Syntax

DBMS_RANDOM.SEED (
   val  IN  BINARY_INTEGER);

DBMS_RANDOM.SEED (
   val  IN  VARCHAR2);

Parameters

Table 6-3 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;

Parameters

Table 6-4 STRING function parameters

Parameter Description

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

opt

What the returning string looks like:

'u', 'U' - Returning string is in uppercase alpha characters.
'l', 'L' - Returning string is in lowercase alpha characters.
'a', 'A' - Returning string is in mixed-case alpha characters.
'x', 'X' - Returning string is in uppercase alpha-numeric characters.
'p', 'P' - Returning string is in any printable characters.

Otherwise the returning string is in uppercase alpha characters.

len

Length of the returned string

Return value

A VARCHAR2 value with the random string

TERMINATE procedure

This procedure is deprecated. Although currently supported, it should not be used. It would be called when the user is finished with the package.

Syntax

DBMS_RANDOM.TERMINATE;

VALUE function

One version returns 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). The other version returns a random Oracle Database NUMBER value x, where x is greater than or equal to the specified low value and less than the specified high value.

Syntax

DBMS_RANDOM.VALUE
  RETURN NUMBER;

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

Parameters

Table 6-5 VALUE function parameters

Parameter	Description
`low`	Lower limit of the range in which to generate a random number
`high`	Upper limit of the range in which to generate a random number

Return value

A NUMBER value that is the generated random number