Load Test Data

Many times development teams asks DBAs to populate test data so that they can perform some performance test.

Below you will find some examples to load test data.

SQL> create table sales
(trans_date date, cust_id int, sales_amount number );

SQL> insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
SQL> commit;

SQL> begin
for i in 1 .. 4
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100
from sales;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

## SELECT A RANDOM VALUE ##
select cust_id from (select cust_id from sales order by dbms_random.value) where rownum=1;

Some other commands using DBMS_RANDOM to generate and populate values

SELECT dbms_random.normal FROM dual;
SELECT ABS(dbms_random.normal) FROM dual;
SELECT (1+ABS(MOD(dbms_random.random,100000))) FROM dual; ==>> force output to +ve values only
SELECT dbms_random.value(2, 3) FROM dual; ==>> Get a value between 2 and 3
## Random String ##
dbms_random.string(opt IN CHAR, len IN NUMBER)
RETURN VARCHAR2;

select dbms_random.string('A', 12) from dual; ==>> random alphanumeric values

opt seed values:
'a','A' alpha characters only (mixed case)
'l','L' lower case alpha characters only
'p','P' any printable characters
'u','U' upper case alpha characters only
'x','X' any alpha-numeric characters (upper)

-- create test data == another example ==
CREATE TABLE test (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

DECLARE
x VARCHAR2(20);
y VARCHAR2(20);
BEGIN
FOR i IN 1..100
LOOP
x := dbms_random.string('A', 20);
y := dbms_random.string('A', 20);

INSERT INTO test
(col1, col2)
VALUES
(x,y);
END LOOP;
COMMIT;
END;
/