Generating Random Data in Oracle

random-numbers-oracleMost 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.
Get our Articles via Email. Enter your email address.

You may also like...

5 Comments

  1. Steven Dyson says:

    I was looking for something like this and i was using the rowNum mod in the where clause but wasn’t pulling exactly what i wanted. This worked well, nice Job!

  2. yamen says:

    thank of your effort

  3. Anjum says:

    create table employee as (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);

  4. Mohammd Shahnawaz says:

    nice job, awesome query, specially when we wanted to popullet the table with dummy data.
    Thank you
    Mohammad Shahnawaz

  5. anonymous says:

    good article

Leave a Reply

Your email address will not be published. Required fields are marked *