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

codeling 1236 - 5298
@2016-01-11 10:41:59

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.


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.

codeling 1236 - 5298
@2016-01-11 10:57:24

STRING Function

This function generates a random string.

   opt  IN  CHAR,
   len  IN  NUMBER)

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.


1. Generating an upper case string of 10 characters

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


2. Generating a lower case string of 10 characters

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


3. Generating an alphanumeric string of 15 characters

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


4.Generating an upper case alphanumeric string of 15 characters

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


5.Generating a string of printable 100 characters

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


codeling 1236 - 5298
@2016-01-11 11:10:55

SEED Procedures

This procedure resets the seed.


   val  IN  VARCHAR2);

The parameter val is a seed number or string used to generate a random number. The seed can be a string up to length 2000.

If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling one of the overloads of DBMS_RANDOM.SEED. To produce different output for every run, simply to omit the call to "Seed" and the system will choose a suitable seed for you.

codeling 1236 - 5298
@2016-01-11 11:18:42

NORMAL Function

This function returns random numbers in a standard normal distribution.



1. Generating a random number in a standard normal distribution

SELECT dbms_random.normal FROM dual;


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

SELECT ABS(dbms_random.normal) FROM dual;

Users browsing this topic