Generating Random Data in Oracle

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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.