How to Generate Random Data (Numbers, Strings and Dates) in Oracle?

Generating random numbers and strings is required in case when there is a need to create a lot of testing data, or when we simply need to use a random number and character combination temporarily in your application. For example, we need a simple mechansim to generate random password strings of a fixed size when creating and maintaining logins for users in a website.

In Oracle, the built-in package dbms_random is provided to facilitate the these tasks. Of couse, you can always write your own random generation logic in PL/SQL if the existing functions don't meet you satisfaction. Even in this case, I would recommend you to write your random generation logic wrapping on Oracle built-in package.

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.

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

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.

SEED -  reset the seed.

NORMAL - generate random numbers in a standard normal distribution.

STRING Function

This function generates a random string.

DBMS_RANDOM.STRING()
   opt  IN  CHAR,
   len  IN  NUMBER)
  RETURN VARCHAR2;

The first parameter specifies what the returning string looks like. Providing any other character will return the output in upper case only.

• 'u', 'U' - returning string in uppercase alpha characters
• 'l', 'L' - returning string in lowercase alpha characters
• 'a', 'A' - returning string in mixed case alpha characters
• 'x', 'X' - returning string in uppercase alpha-numeric characters
• 'p', 'P' - returning string in any printable characters.

 The second parmaeter is the size of return string.

Exampels

1. Generating an upper case string of 10 characters

select dbms_random.string('U', 10) from dual;

HXUNQVAPMH

2. Generating a lower case string of 10 characters

select dbms_random.string('L', 10) from dual;

oelqemcuvc

3. Generating an alphanumeric string of 15 characters

select dbms_random.string('A', 15) from dual;

nTaZMOMozatqsXY

4.Generating an upper case alphanumeric string of 15 characters

select dbms_random.string('X', 15) from dual;

HBU7GFIGGH3MMDC

5.Generating a string of printable 100 characters

select dbms_random.string('P', 100) from dual;

/sJ(HQ1":[>uD>h#^gMDyT=:S7L++YZF^JX<?#Mf)[hWi2T,3{LT1`Zb+v3S+lIx'v2dA6jhD6|cfqFf3y`<;6)iBloKpv`0Sz=+

NORMAL Function

This function returns random numbers in a standard normal distribution.

DBMS_RANDOM.NORMAL
  RETURN NUMBER;

Example

1. Generating a random number in a standard normal distribution

SELECT dbms_random.normal FROM dual;

-1.30052371356914119220661730329227733501

2. Generating a random number in a standard normal distribution and converting it to positive number

SELECT ABS(dbms_random.normal) FROM dual;

0.4075333293453310040873615409212579984467