The DBMS_RANDOM package provides a number of methods to generate random values. The following table lists the functions and stored procedures that are available in the DBMS_RANDOM package.

Table 1. DBMS_RANDOM functions and stored procedures
Function/stored procedure Return type Description
INITIALIZE(val) N/A Initializes the DBMS_RANDOM package with the specified seed value. Deprecated, but supported 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. Deprecated, but supported for backward compatibility.
SEED(val) N/A Resets the seed with the specified value.
SEED(val) N/A Resets the seed with the specified value.
STRING(opt, len) VARCHAR2 Returns a random string.
TERMINATE N/A Has no effect. Deprecated, but supported for backward compatibility.
VALUE NUMBER Returns a random number with a value greater than or equal to 0 and less than 1, with 38 digit precision.
VALUE(low, high) NUMBER Returns a random number with a value greater than or equal to low and less than high.

INITIALIZE

The INITIALIZE stored procedure uses a seed value to initialize the DBMS_RANDOM package. Syntax:
INITIALIZE(val IN INTEGER)

The INITIALIZE stored procedure can be considered deprecated because it is only included for backward compatibility.

Parameters

Parameter Description
val The seed value used by the DBMS_RANDOM package algorithm.

Examples

The following code snippet demonstrates a call to the INITIALIZE stored procedure that initializes the DBMS_RANDOM package with the seed value, 6475.

DBMS_RANDOM.INITIALIZE(6475);

NORMAL

The NORMAL function returns a random number of type NUMBER. Syntax:

result NUMBER NORMAL()

Parameters

Table 2.
Parameter Description
result A random value of type NUMBER.

Examples

The following code snippet demonstrates a call to the NORMAL function:

x:= DBMS_RANDOM.NORMAL();

RANDOM

The RANDOM function returns a random INTEGER value that is greater than or equal to -2 ^31 and less than 2 ^31. Syntax:

result INTEGER RANDOM()

The RANDOM function can be considered deprecated because it is only included for backward compatibility.

Parameters

Table 3.
Parameter Description
result A random value of type INTEGER.

Examples

The following code snippet demonstrates a call to the RANDOM function. The call returns a random number:

x := DBMS_RANDOM.RANDOM();

SEED

The SEED stored procedure resets the seed value for the DBMS_RANDOM package by using a string value. Syntax:

SEED(val IN VARCHAR2)

Parameters

Parameter Description
val The val parameter is the seed value used by the DBMS_RANDOM package algorithm.

Examples

The following code snippet demonstrates a call to the SEED stored procedure. The call sets the seed value to abc123.

DBMS_RANDOM.SEED('abc123');

STRING

The STRING function returns a random VARCHAR2 string in a user-specified format. Syntax:

result VARCHAR2 STRING(opt IN CHAR, len IN NUMBER)

Parameters

opt: The formatting option for the returned string. The following table lists possible values of the option parameter.
Option Description
u or U Uppercase alpha string
l or L Lowercase alpha string
a or A Mixed case string
x or X Uppercase alpha-numeric string
p or P Printable characters

len: The length of the returned string.

result: The result parameter is a random value of type VARCHAR2.

Examples

The following code snippet demonstrates a call to the STRING function. The call returns a random alpha-numeric character string that is 10 characters in length.

x := DBMS_RANDOM.STRING('X', 10);

TERMINATE

The TERMINATE stored procedure has no effect. Syntax:

TERMINATE

We do not recommend that you use the TERMINATE stored procedure because it is only supported for compatibility.

VALUE

The VALUE function returns a random NUMBER that is greater than or equal to 0, and less than 1, with 38 digit precision. The VALUE function has two forms. The syntax of the first form is:

result NUMBER VALUE()

Parameters

Parameter Description
result A random value of type NUMBER.

Examples

The following code snippet demonstrates a call to the VALUE function. The call returns a random NUMBER:

x := DBMS_RANDOM.VALUE();

VALUE

The VALUE function returns a random NUMBER with a value that is between user-specified boundaries. The VALUE function has two forms. The syntax of the second form is:

result NUMBER VALUE(low IN NUMBER, high IN NUMBER)

Parameters

Parameter Description
low The lower boundary for the random value. The random value may be equal to low.
high The upper boundary for the random value. The random value will be less than high.
result A random value of type NUMBER.

Examples

The following code snippet demonstrates a call to the VALUE function. The call returns a random NUMBER with a value that is greater than or equal to 1 and less than 100.

x := DBMS_RANDOM.VALUE(1, 100);