Search code examples
databaseoracleplsqlplsqldevelopertest-data

Generate test data using Oracle PL/SQL developer

I want to test some schemas and indexes, and I was wondering if there is a functionality in PL/SQL Developer that can generate test data (so I won't have to create sequences and loops to insert data in the tables).

Solution

  • Loops and PL/SQL aren't always necessary; this trick might be helpful:

    insert into emp(id, name, salary)
select rownum, 'Employee ' || to_char(rownum), dbms_random.value(2, 9) * 1000
from dual
connect by level <= 100;

    will generate 100 records, named Employee 1 through Employee 100 with random "round" salaries between 2000 and 9000.

    The two main techniques are:

    1. Use of connect by level <= n to generate n rows in a query on dual.
    2. Use of dbms_random package; there's also a very useful function dbms_random.string which can be used -- like its name suggests -- to generate random strings of a certain length containing certain characters.