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;


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.

Comments

Post a Comment

Popular posts from this blog

Swift Learning Roadmap

Image
Recently one of my email subscribers wrote me this: What I find is that I’m learning the various steps but I do not understand the “big picture”. For me, a guideline, would be helpful. I know Swift is available but I do not even know object oriented programming. I’d really like to see a “roadmap” that says: if you know or do not know this, start here and learn this, then this, etc. He is not alone. Talking to people and reading comments online I found that many share the same feeling. There is a huge amount of information about iOS development and so many things to learn. It is easy to get lost and not know what is important to know and what should be learned first. I know that feeling myself. When I started adventuring into Mac OS X programming in 2005 (the iPhone did not exist yet) I found myself in the same situation. Back then, resources were scarcer than nowadays. I relied a lot on Apple’s documentation and started venturing into the world of Cocoa and...
Read more

How to Apply for an iOS Developer Job

This is the first part of a three part series on applying and interviewing for an iOS developer job. In this first part of the series, you will focus on the applying part of the equation – i.e. preparing your resume and cover letter. In this second part of the series, Ray will show you a few examples of some iOS resumes and cover letters he likes that might be useful for reference or models. In the third part of the series, you will focus on the interviewing part of the equation – i.e. what types of questions to expect and prepare for, and how to do your best in an interview. Note that none of these articles will focus on how to get the required iOS skills (although that might be a good follow-up article some day). Let’s assume you’re already a seasoned iOS developer. After all, if you’re reading this blog, that’s a good sign that honing your iOS skills is important to you! :] Also, none of these articles will focus on where to find iOS jobs (although that’s another...
Read more

Partitioning and Naming Drives (Windows 8)

Image
Understanding drives, partitions, and volumes A partition is an area of a hard disk that can be formatted and assigned a drive letter. On a hard disk, a volume is a formatted primary partition or logical drive. The terms partition and volume are often used interchangeably. HP and Compaq computers with Windows 8 have hard disk drives that contain at least two partitions: the main partition for Windows and your work, and a recovery partition to restore the computer back the way it came from the factory. Here is a list of some of the partitions you might find on your hard drive: The main partition, usually labeled with the letter C, contains the system files, program files, and usable file storage space. A recovery partition, usually drive letter D and labeled RECOVERY, contains system recovery information in case the files on the C partition are damaged or unusable. A hidden partition might exist called SYSTEM. A SYSTEM partition is used by Wind...
Read more