The DBMS_RANDOM package provides a number of methods to generate random values.

The following table describes the functions and stored procedures that are available in the DBMS_RANDOM package.

Table 1. DBMS_RANDOM functions and stored procedure
Function or stored procedure Return value type Description
INITIALIZE(val) N/A Initializes the DBMS_RANDOM package with the specified seed value. It is deprecated and only included for backward compatibility.
NORMAL() NUMBER Returns a random NUMBER.
RANDOM INTEGER Returns a random INTEGER, which is greater than or equal to -2A31 and less than 2A31. It is deprecated and only included for backward compatibility.
SEED(val) N/A Resets the seed of the DBMS_RANDOM package with a string value.
STRING(opt, len) VARCHAR2 Returns a random VARCHAR2 string in the custom format.
TERMINATE N/A Terminates the DBMS_RANDOM package. It is deprecated and only included for backward compatibility.
VALUE NUMBER Returns a random NUMBER. The value must be greater than or equal to 0 and less than 1, with a precision of 38 digits.
VALUE(low, high) NUMBER Returns a random NUMBER which is greater than or equal to low and less than high.

INITIALIZE

The INITIALIZE stored procedure used to initialize the DBMS_RANDOM package with a seed value. Syntax:
INITIALIZE(val IN INTEGER)
Parameters
Parameter Description
val The seed value used by the DBMS_RANDOM package.
Examples
The following block demonstrates a call to the INITIALIZE stored procedure that initializes the DBMS_RANDOM package with the seed value of 6475.
DBMS_RANDOM.INITIALIZE(6475);

NORMAL

The NORMAL function is used to return a random NUMBER. Syntax:
result NUMBER NORMAL()
Parameters
Parameter Description
result A random value of the NUMBER type.
Examples
x:= DBMS_RANDOM.NORMAL();

RANDOM

The RANDOM function is used to return a random INTEGER which is greater than or equal to -2A31 and less than 2A31. Syntax:
result INTEGER RANDOM()
Parameters
Parameter Description
result A random value of the INTEGER type.
Examples
x := DBMS_RANDOM.RANDOM();

SEED

The SEED stored procedure is used to reset the seed value for the DBMS_RANDOM package by using a string value. Syntax:
SEED(val IN VARCHAR2)
Parameters
Parameter Description
val The seed value used by the DBMS_RANDOM package.
Examples
The following block demonstrates a call to the SEED stored procedure which uses the seed value of abc123.
DBMS_RANDOM.SEED('abc123');

STRING

The STRING function is used to return a random VARCHAR2 string in the custom format. Syntax:
result VARCHAR2 STRING(opt IN CHAR, len IN NUMBER)
Request parameters
Parameter Description
opt The format for the returned string. Valid values:
  • u or U: a string of uppercase letters.
  • l or L: a string of lowercase letters.
  • a or A: a string of both uppercase and lowercase letters.
  • x or X: a string of both uppercase letters and numerics.
  • P or P: a string of any printable characters.
len The length of the returned string.
Response parameters
Parameter Description
result A random string of the VARCHAR2 type.
Examples
The following block demonstrates a call to the STRING function which returns a random alpha-numeric character string that is 10 characters in length.
x := DBMS_RANDOM.STRING('X', 10);

TERMINATE

The TERMINATE stored procedure is used to terminate the DBMS_RANDOM package. Syntax:
TERMINATE

VALUE

The VALUE function is used to return a random NUMBER. The random value is greater than or equal to 0 and less than 1, with a precision of 38 digits. Or the random value falls within the specified boundaries. The VALUE function has two syntax forms:
result NUMBER VALUE();
result NUMBER VALUE(low IN NUMBER, high IN NUMBER);
Request parameters
Parameter Description
low Specifies the lower boundary of a random value. The random value may be equal to low.
high Specifies the upper boundary of a random value. The random value must be less than high.
Response parameters
Parameter Description
result A random value of the NUMBER type.
Examples
x := DBMS_RANDOM.VALUE();
x := DBMS_RANDOM.VALUE(1, 100);