web analytics
How to Generate Random Data (Numbers, Strings and Dates) in Oracle? Options
codeling
Posted: Monday, January 11, 2016 10:41:59 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

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.

Sponsor
Posted: Monday, January 11, 2016 10:41:59 AM
codeling
Posted: Monday, January 11, 2016 10:57:24 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

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=+

codeling
Posted: Monday, January 11, 2016 11:10:55 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

SEED Procedures

This procedure resets the seed.

DBMS_RANDOM.SEED (
   val  IN  BINARY_INTEGER);

DBMS_RANDOM.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
Posted: Monday, January 11, 2016 11:18:42 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 978
Points: 4113

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
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.