System and Application Softwares
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.
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.
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.
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;
This procedure resets the seed.
val IN BINARY_INTEGER);
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.
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;
© 2020 Digcode.com. All rights reserved.