DBMS_RANDOM

The DBMS_RANDOM package provides functions and stored procedures for generating random values in PolarDB for Oracle.

Overview

Function or stored procedure	Return type	Description
`INITIALIZE(val)`	N/A	Initializes the `DBMS_RANDOM` package with a seed value. Deprecated. Use `SEED` instead.
`NORMAL()`	NUMBER	Returns a random NUMBER.
`RANDOM`	INTEGER	Returns a random INTEGER in the range [-2^31, 2^31). Deprecated. Use `VALUE` instead.
`SEED(val)`	N/A	Resets the seed of the `DBMS_RANDOM` package.
`STRING(opt, len)`	VARCHAR2	Returns a random VARCHAR2 string in a specified format.
`TERMINATE`	N/A	Terminates the `DBMS_RANDOM` package. Deprecated. Included for backward compatibility only.
`VALUE`	NUMBER	Returns a random NUMBER in the range [0, 1) with a precision of 38 digits.
`VALUE(low, high)`	NUMBER	Returns a random NUMBER in the range [low, high).

Deprecated subprograms

The following subprograms are deprecated. They are included for backward compatibility only. Avoid using them in new applications.

INITIALIZE: Use SEED instead.
RANDOM: Use VALUE instead.
TERMINATE: Deprecated and included for backward compatibility only.

INITIALIZE

INITIALIZE is deprecated. Use SEED instead.

Initializes the DBMS_RANDOM package with a seed value.

Syntax:

INITIALIZE(val IN INTEGER)

Parameters

Parameter	Description
`val`	The seed value (INTEGER) for the `DBMS_RANDOM` package.

Example

DBMS_RANDOM.INITIALIZE(6475);

NORMAL

Returns a random NUMBER.

Syntax:

result NUMBER NORMAL()

Return value

Name	Type	Description
`result`	NUMBER	A random value of the NUMBER type.

Example

x := DBMS_RANDOM.NORMAL();

RANDOM

RANDOM is deprecated. Use VALUE instead.

Returns a random INTEGER in the range [-2^31, 2^31).

Syntax:

result INTEGER RANDOM()

Return value

Name	Type	Description
`result`	INTEGER	A random integer in the range [-2^31, 2^31).

Example

x := DBMS_RANDOM.RANDOM();

SEED

Resets the seed of the DBMS_RANDOM package.

Syntax:

SEED(val IN VARCHAR2)

Parameters

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

Example

DBMS_RANDOM.SEED('abc123');

STRING

Returns a random VARCHAR2 string in a specified format.

Syntax:

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

Parameters

Parameter	Description
`opt`	The format of the returned string. Valid values are listed in the following table.
`len`	The length of the returned string.

Valid values for `opt`

Value	Description
`u` or `U`	Uppercase letters only
`l` or `L`	Lowercase letters only
`a` or `A`	Mixed-case letters
`x` or `X`	Uppercase letters and digits
`p` or `P`	Any printable characters

Return value

Name	Type	Description
`result`	VARCHAR2	A random string in the specified format.

Example

The following example returns a random 10-character alphanumeric string:

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

TERMINATE

TERMINATE is deprecated. It is included for backward compatibility only.

Syntax:

TERMINATE

VALUE

Returns a random NUMBER. Has two forms:

With no arguments: returns a value in the range [0, 1) with a precision of 38 digits.
With low and high: returns a value in the range [low, high).

Syntax:

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

Parameters

Parameter	Description
`low`	The lower bound of the range. The returned value may equal `low`.
`high`	The upper bound of the range. The returned value is always less than `high`.

Return value

Name	Type	Description
`result`	NUMBER	A random value in the specified range.

Examples

x := DBMS_RANDOM.VALUE();        -- Returns a value in [0, 1)
x := DBMS_RANDOM.VALUE(1, 100);  -- Returns a value in [1, 100)