Untitled Document

Using SQL*Loader to ganerate sample data

This example will show how to use SQL*Loader to generate sample data for testing purposes. This is useful when trying to generate a bunch of records for testing database. Instead of using pl/sql or buying a third party software, you can use SQL*Loader. It will do a desent job, depending how fancy you want to get with the control file and will load the data very quickly. Using the example below, you can generate virtually any kind of data. For this example, lets say you have this table:

   create table customer
       (id number,
       name varchar2(100),
       street_address varchar2(100),
       birth_date date,
       education_level varchar2(100)
    );

Use the following SQL*Loader control file to load it:

LOAD
TRUNCATE
INTO TABLE FOO_TABLE2
(
id SEQUENCE(1000),
name EXPRESSION "decode(ceil(dbms_random.value(0,10)),1,'James',2,'John',3,'Robert',4,'Michael',5,'William',6,'Mary',7,'Patricia',8,'Linda',9,'Barbara',10,'Susan')||' '||decode(ceil(dbms_random.value(0,10)),1,'Smith',2,'Johnson',3,'Williams',4,'Jones',5,'Brown',6,'Davis',7,'Miller',8,'Wilson',9,'Moore',10,'Taylor')",
street_address EXPRESSION "ceil(dbms_random.value(0,10000))||' '||decode(ceil(dbms_random.value(0,10)),1,'Washington',2,'Jackson',3,'Adams',4,'Main',5,'Park',6,'Seventh',7,'Maple',8,'Pine',9,'Hill',10,'Second')||' '||decode(ceil(dbms_random.value(0,3)),1,'St',2,'Ave',3,'Blvd')",
birth_date EXPRESSION "trunc(SYSDATE-dbms_random.value(10000,32850))",
education_level EXPRESSION "decode(ceil(dbms_random.value(0,6)),1, 'High school', 2, 'Associate degree', 3, 'Bachelors degree', 4, 'Postgraduate', 5,'PhD, law or medical degree',6,'High school dropout')"
)

the sample generated data looks like this:

   id,name,street_address,birth_date,education_level

   1000,Susan Williams,4493 Seventh Blvd,21-MAR-28,High school
   1001,Mary Davis,6879 Main Blvd,19-JUN-77,PhD, Postgraduate
   1002,Michael Wilson,9465 Hill St,02-AUG-31,PhD, law or medical degree
   1003,Susan Wilson,3649 Adams Blvd,06-JUN-59,Associate degree
   1004,Mary Davis,3718 Main St,12-FEB-81,High school
   1005,Linda Johnson,9317 Pine St,23-SEP-40,Bachelors degree
   1006,William Jones,2705 Park Ave,17-SEP-74,High school dropout
   1007,Barbara Johnson,273 Jackson Blvd,07-FEB-24,Associate degree
   1008,James Moore,8825 Seventh Ave,21-SEP-59,Postgraduate