Square DBA – Oracle DBA Talk

Generating random numbers and strings in Oracle

Do you know how to auto generate random numbers or strings in Oracle? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size. If you need random numbers or string then DBMS_RANDOM is your friend.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the /rdbms/admin directory.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM – generate random numbers.

VALUE – generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.

STRING – generate strings in upper case, lower case or alphanumeric format.

The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U – Upper case
L – Lower case
A – Alphanumeric
X – Alphanumeric with upper case alphabets.
P – Printable characters only.
Providing any other character will return the output in upper case only.

The size of the string should also be provided as the second parameter.

Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.

INITIALIZE – Initialize the package to proceed with the number generation.

Provide a number (seed) as input to the routine.

SEED – Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.

TERMINATE – Close the process of random number generation. More »