Efficiently creating test data with Oracle

When working with an Oracle database, you may have the need to create some test data.
As DUAL contains just a single row, you often abuse ALL_OBJECTS as source for your statement.

But even ALL_OBJECTS has its limitation regarding the number of rows, so what worked for a few thousands rows will suddenly stop working for millions of rows - and then you have to switch to PL/SQL or to another programming language or your choice for this simple task.

But there is a solution in pure Oracle SQL by using hierarchical queries. Have a look at the following statement which helps you to generate as much rows as you want:

-- Using a hierarchical query to generates 1000 rows
select level id from dual
  connect by level <= 1000;

Note that level starts with 1, so the query above will create the numbers from 1 to 1000.

You can then you use CREATE TABLE AS or INSERT with a subquery to efficiently create your test data. The needed row data you can easily generate from the LEVEL value by using functions like MOD, DECODE or a CASE statement.

The following example creates a test data with 3 columns and 1000 rows:

create table t1 as
  select
    level id,
    mod(level, 10) count10,
    case when mod(level, 10) = 0 then
      '----'
    else
      lpad(level, 4, '-')
    end text
  from dual
  connect by level <= 1000;