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.
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
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
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
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);