Most of the times, production data is not available in development environments. Here, I would like to share a single sql command which can generate random data. But before that, let’s address another issue faced by a lot of new oracle users. We need to generate a sequence of numbers using a sql statement. This will generate a number sequence. Nothing fancy, simplest of sql known :) Here it is:
SELECT ROWNUM FROM DUAL CONNECT BY LEVEL < 10000;
Now, to generate the random data, the oracle build in package “dbms_random” comes handy. I am referring to the version 10GR2, there might be additional features available on 11G . Other build in functions can also be used. Here is a sample sql:
SELECT LEVEL empl_id, MOD (ROWNUM, 50000) dept_id, TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary, DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender, TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28)) || '-' || ROUND (DBMS_RANDOM.VALUE (1, 12)) || '-' || ROUND (DBMS_RANDOM.VALUE (1900, 2010)), 'DD-MM-YYYY' ) dob, DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address FROM DUAL CONNECT BY LEVEL < 10000;
You can create a table using this sql and that will give you some random test data to work on. The output looks something like:
EMPL_ID | DEPT_ID | SALARY | GENDER | DOB | ADDRESS 1 | 1 | 385433.6 | M | 2/25/1903 | VVGJOPVIHD8HZELHK1SXWQ1RTNK84NT6 2 | 2 | 363024.64 | F | 5/24/2010 | E4IOCU42LM7K2SS36OI0STDOO7A2UZ50L2Q5R1SME07 3 | 3 | 320010.48 | M | 9/26/2009 | 8XM6CG3CSR6UA26PXPUTLPLPQNSQ3OJG7P0CL4XVHBMCVT 4 | 4 | 64230.96 | F | 6/26/1991 | GLKYLEWG4NS0G67W64LF1G5GJPON5L8K93F 5 | 5 | 414134.44 | M | 10/26/1981| QWXIT92XPEYYZZ0A8MR050ER8UJ30NYUHDJAEJHF2M3 ... ...
There are numerous functions which can be used to generate randomized data in different ways. Best is to create a wrapper package which can generate number, string, date etc based on the parameter passed and then call that package. I will share it as soon as I am done writing it.