Generate test data using Oracle PL/SQL developer

database oracle plsql plsqldeveloper test-data
eyettea picture eyettea · Oct 16, 2013 · Viewed 11.2k times · Source

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).

Answer

Colin 't Hart picture Colin 't Hart · Oct 16, 2013

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.

Related questions

create table with sequence.nextval in oracle

i created a sequence using the following query, create sequence qname_id_seq start with 1 increment by 1 nocache; Now when i try to create a table which uses the above sequence, it is throwing the following error, Error report: SQL …

Read More
How can I select records ONLY from yesterday?

I've spent hours searching the web for an answer to this question... Here's what I currently have: select * from order_header oh where tran_date = sysdate-1

Read More
ORA-00060: deadlock detected while waiting for resource

I have a series of scripts running in parallel as a nohup on an AIX server hosting oracle 10g. These scripts are written by somebody else and are meant to be executed concurrently. All the scripts are performing updates on …

Read More