Quickly generate sample data with PL/SQL

Whenever I need some sample data in a table for which I’m developing something or trying something out I use a basic and simple PL/SQL script. 

And, which place is better than this blog to share it with you, right? See below for an example of a custom user sample table which I use quite a lot to play around with BI related things.

So fire up your SQL Developer or other tool and start creating sample tables.

CREATE TABLE user_sample_data (
 id          NUMBER(10)    NOT NULL,
 first_name  VARCHAR2(40)  NOT NULL,
 last_name   VARCHAR2(40)  NOT NULL,
 gender      VARCHAR2(1),
 dob         DATE,
 last_login  DATE
);

DECLARE 
  gender user_sample_data.gender%TYPE;
  dob user_sample_data.dob%TYPE;
  last_login user_sample_data.last_login%TYPE;

BEGIN
  FOR cur_rec IN 1 .. 100000 LOOP
     /* select random male or female */
     SELECT substr('MF', dbms_random.value(1,3), 1) INTO gender FROM DUAL;
     
     /* select random date between 1980 and now */
     SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(DATE '1980-01-01','J')
                                    ,TO_CHAR(SYSDATE,'J')
                                    )
                    ),'J') INTO dob FROM DUAL;
     
     /* select random date between now and 2 weeks ago */
     SELECT TO_DATE(
              TRUNC(
                   DBMS_RANDOM.VALUE(TO_CHAR(SYSDATE-14,'J')
                                    ,TO_CHAR(SYSDATE,'J')
                                    )
                    ),'J') INTO last_login FROM DUAL;
     
     /* insert sample record*/
     INSERT INTO user_sample_data 
      VALUES (cur_rec, 
              'Simon' || cur_rec, 
              'Sample', 
              gender, 
              dob, 
              last_login);
      COMMIT;
  END LOOP;
END;
/

Of course the CREATE TABLE part (lines 1-8) defines the table itself. The script inserts, in this case, 100.000 records in the table with some random genders and dates. Adjust the last number on line 16 to add less or more records to your table.

As you can see in line 18 a random gender is selected from a M(ale)F(emale) string. The date of birth or dob will hold a date between 1980 and now (line 21). The last_login variable will hold a date between now and 2 weeks ago (line 29).

Of course, adjust the script where needed to fit your needs and happy coding!