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.