Random Data Generation

I was asked by a friend at another company if Oracle provides a tool for generating random data so he can fill up some test tables to test their index strategy with a large amount of data in the system. He is a "Developer" but I do not hold that against him, he is an upstanding guy and committed to his job. Instead of simply answering him, I decided to post what I was going to send. After fighting with blogger to get the layout the way I wanted, and moving from a post, to a linked HTML file, back to a post and trying out various free blog writing tools. I basically gave up and started using Zoundry, it does a fine job but I still can't get blogger to display the output the way I wanted. So be it...

Oracle provides the DBMS_RANDOM package for generating random numbers and even random strings. The package is available in oracle 9 or higher.

Random Numbers
The function DBMS_RANDOM.VALUE can be used for generating random numbers. If you do not pass any parameters the number generated is between 0 and 1.

SELECT DBMS_RANDOM.VALUE VALUE FROM DUAL;

This will generate a random number between 1 and 100.

SELECT DBMS_RANDOM.VALUE(1,100) VALUE FROM DUAL;

You can use ROUND or TRUNC to remove the decimals.

SELECT ROUND(DBMS_RANDOM.VALUE(1,100)) VALUE FROM DUAL;

This will generate a random number between 1000 and 9999.

SELECT ROUND(DBMS_RANDOM.VALUE(1000, 9999)) VALUE FROM DUAL;

Random Strings
You can use DBMS_RANDOM.STRING to generate random strings. The first parameter is OPT which allows you some control over the type of characters being generated. The following are the allowable values to be passed as the OPT parameter.

'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters

The second parameter is the number of characters to be generated. The maximum value is 2000.
The following will generate 30 random uppercase characters.

SELECT DBMS_RANDOM.STRING('U',30) VALUE FROM DUAL;

Generate 30 random printable characters

SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL;

Lets say we want to generate a little more real world data for testing. We can generate a table of 50 rows of random data very easily, I tend to use the 'P' parameter because you get all of the printable characters so your test data is loaded with ampersands '&' and comma's ','.

CREATE TABLE TEST_DATA AS SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL CONNECT BY LEVEL<=50;
SELECT * FROM TEST_DATA WHERE ROWNUM <= 5;

VALUE
------------------------------------------------------------------
71E/BQh;3[WU&f -d7IV22ZA".mNDi
j$ZG|(A^'YHL6A1N!n"q$|HID:A3^g
<T?n$U]GF(f.c_xC`[/%!qLm40>{[[
o]3FHL#shWVh|fR|A*&S?B&+VPd^j_
2M%YN0[gTa{7D-kTomx:c^>bgF.w%3
5 row(s) retrieved

Lets generate random lengths of data by combining the functions together

CREATE TABLE TEST_DATA_RAND_LENGTHS AS SELECT DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)) VALUE FROM DUAL CONNECT BY LEVEL<=50;
SELECT * FROM TEST_DATA_RAND_LENGTHS WHERE ROWNUM <= 5;

VALUE                          LENGTH(VALUE)
------------------------------ --------------------------
}+7ym-LI%DqHq                  13
Oki\!<4)UVDAubJ4Y?P^o          21
7(KFuAVN{v<_,XJefe             19
L-\9B&iV#b%*                   12
z2_|Wk1!q7PxAH2zYb|kMuZ        23

Ok, That is great you say, but what can you really do with that? If you want to see a SQL script that creates some tables and generates a few million rows of data, please click here.
That was just a quick introduction to the random number and character generation that is provided by oracle. Nothing new or innovative there, but something to plant the seed in your memory to grow and be used at a later time.To clone and fill an existing table, Thomas Kyte has an excellent procedure for doing this. Please See generate test data automagically