Saturday, 14 February 2009

How To Generate Random Values in Oracle

Oracle provides the DBMS_RANDOM package for generating random data. The VALUE function returns a random number and the STRING function returns a random string.

VALUE

The VALUE function returns a random number in the specified range. If no range is provided, a value greater than or equal to zero and less than one is returned.

For example, we can get a random value between zero and one.
SELECT dbms_random.value rnd FROM dual;

RND
-----------------------------------------
0.6573975346756855362691307891314972871

1 rows selected

Or, we could specify a range to return a value between, say, ten and twenty.
SELECT dbms_random.value(10, 20) rnd FROM dual;

RND
------------------------------------------
17.0948463090004580455301715474853817198

1 rows selected
Add a FLOOR, CEIL or ROUND function to return an integer.
SELECT floor(dbms_random.value(10, 20)) rnd FROM dual;

RND
-------
14

1 rows selected

STRING

The STRING function is used to get a random string.

The following options are used to specify the type of string to return.
  • 'u' or 'U' returns uppercase alpha characters
  • 'l' or 'L' returns lowercase alpha characters
  • 'a' or 'A' returns mixed case alpha characters
  • 'x' or 'X' returns uppercase alpha-numeric characters
  • 'p' or 'P' returns any printable characters
For example, to get a random string of 20 uppercase alphanumeric characters, we use the following SQL.
SELECT dbms_random.string('X', 20) rnd FROM dual;

RND
----------------------
75JM2IQUK4NL91NR4GIR

1 rows selected

To return a 30 character, mixed case string, we use this command.
SELECT dbms_random.string('A', 30) rnd FROM dual;

RND
--------------------------------
auzZoOfpahvMzaDXqcpMSuxOGyraPb

1 rows selected

Notes on DBMS_RANDOM

  • The SEED procedure can be used to reset the seed for the package. The same set of random values are returned if an identical value is used for the seed. This can be useful during testing.
  • If no seed is specified then DBMS_RANDOM is automatically initialized with the date, userid, and process id.
  • The INITIALIZE, RANDOM and TERMINATE functions are obsolete.

5 comments:

Anonymous said...

I like reading your blog because you can always bring us fresh and cool stuff, I feel that I should at least say thanks for your hard work.

- Henry

Anonymous said...

Hey I think you have a great blog going here, I found it on Bing and plan on returning regularly for the information that you all are providing.

Anonymous said...

C'mon, not much value to this blog...you can't just say use a "ceiling, floor, or round function" without a bit more explanation that, for example, THEY ARE NOT INTERCHANGEABLE. ARG! Useless web stuff...

rajesh pachiyappan said...

your words are very simple and it's self explanatory...enjoy reading your blocks...

Regards,
Rajesh Pachiyappan

rajesh pachiyappan said...

your words are very simple and it's self explanatory...enjoy reading your blocks...

Regards,
Rajesh Pachiyappan