Oracle SQL: Generate (an arbitrary amount of) rows/data

Selecting from DBA_OBJECTS

My current installation has 64974 records in dba_objects. Thus, I can abuse dba_objects as a row source to generate up to 64974 records.

In order to create a table with 50000 records, I can limit the number or generated numbers with where rownum <= 50000:

create table tq84_xyz as
select
   date '1987-01-01' + rownum          dt,
   round(dbms_random.value(1, 100), 2) num
from
   dba_objects
where
   rownum <= 50000;

If more than 64K records need to be produced, dba_objects can be cross joined to itself. This produces a cartesian product, i. e. can provide up toe 64974*64974 records.

The following statement creates a table with a record for each second in the year 2023:

drop   table tq84_xyz;

create table tq84_xyz as
select
   date '2023-01-01' + rownum/24/60/60    dt,
   rownum                                 num
from
   dba_objects cross join dba_objects
where
   rownum <= 365*24*60*60 -1
;

select
   min(dt),
   max(dt),
   count(*)
from
   tq84_xyz;

Using CONNECT BY without START WITH

A given number of records can also be generated with the start with … connect by clause.

The following statement generates 15 rows:

select
   rownum
from
   dual connect by rownum <= 14;

See also the CONNECT BY WITHOUT FILTERING SQL plan operator and the error message ORA-30009: Not enough memory for CONNECT BY operation.

XMLTable

A series of numbers can be created with XMLTable:

select
   rownum
from
   xmlTable('1 to 42');

Using the MODEL clause

Rows can also be generated using the model clause, an example demonstrating this is here.