Creating rows from nothing – time for something better

February 19, 2025

2–3 minutes

Nine(!) years ago I published one of the first videos on my YouTube channel about quickly generating rows from the database

and not much has really changed since then when it comes generating rows. Most of us are now familiar with the CONNECT FROM trick using DUAL.

But if like me, you are sick and tired of spending your days typing “SELECT .. FROM DUAL CONNECT BY LEVEL <“, here is an easy way to make your life easier.

We can use a SQL macro!


SQL> create or replace
  2  function grows(i int) return varchar2 sql_macro is
  3  begin
  4    if i <= 100000 then
  5      return 'select rownum n from dual connect by level <= i';
  6    elsif i <= 10000000000 then
  7      return 'select rownum n from '||
  8             '( select 1 from dual connect by level <= 100000 ),'||
  9             '( select 1 from dual connect by level <= i/100000) where rownum <= i';
 10    else
 11      return 'select rownum n from '||
 12             '( select 1 from dual connect by level <= 100000 ),'||
 13             '( select 1 from dual connect by level <= 100000 ),'||
 14             '( select 1 from dual connect by level <= i/10000000000) where rownum <= i';
 15    end if;
 16  end;
 17  /

Function created.

Now it becomes a trivial SQL clause


SQL> select * from grows(3);

         N
----------
         1
         2
         3

SQL> select count(*) from grows(1000000);

  COUNT(*)
----------
   1000000

I chose “GROWS” as the name as a shorthand for “Generate ROWS” and I like the dual meaning that it will “grow” the result set.

If you’re wondering about the little extra complexity in the macro definition, it is because building huge hierarchy result sets can be a strain on your PGA, so if you exceed 100,000 we’ll just cartesian join 2 hierarchies to build the resultset, and go up to 3 hierarchies if you go past 10 billion

Now you can just slot that function in wherever you need it.


SQL> create table t as
  2  select d.* from dba_objects d, grows(5);

Table created.

Obviously you are not forced to use DUAL here. Any technique you prefer could be swapped in, for example:


SQL> create or replace
  2  function grows(i int) return varchar2 sql_macro is
  3  begin
  4    return 'select column_value n from xmltable(''1 to '||i||''')';
  5  end;
  6  /

Function created.

SQL>
SQL> select * from grows(5);

N
-----------------------------------------------------------------------
1
2
3
4
5

Addenda: Shout out to colleague Chris Saxon for pointing out that if you want to use this with a bind variable as input, then all of the logic must be folded directly into the resultant SQL statement. So you would need something like this



SQL> create or replace
  2  function grows2(i int) return varchar2 sql_macro is
  3  begin
  4    return
  5        'select rownum n
  6        from
  7             ( select 1 from dual connect by level <= case when i <= 100000 then i else 100000 end),
  8             ( select 1 from dual connect by level <= case when i >= 100001 then 100000 else 1 end ),
  9             ( select 1 from dual connect by level <= case when i > 100000000 then i/100000000 else 1 end)
 10        where rownum <= i';
 11  end;
 12  /

Function created.

SQL>
SQL> variable i number
SQL> exec :i := 10

PL/SQL procedure successfully completed.

SQL> select count(*) from grows2(:i);

  COUNT(*)
----------
        10

SQL> exec :i := 500050

PL/SQL procedure successfully completed.

SQL> select count(*) from grows2(:i);

  COUNT(*)
----------
    500050

3 responses to “Creating rows from nothing – time for something better”

Bob Bryla

February 19, 2025 at 12:35 pm
I have almost forgotten about DUAL. Even though table macro functionality is available in 19c and the rest in 21c, I thought I should go all out with 23ai. This function is a slightly modified version of yours called gen23ai (generative numeric AI, get it?). You can see where the optimizations are, and of course the output is the same.

create or replace function gen23ai(i int) return varchar2 sql_macro is begin if i <= 100000 then return ‘select rownum n connect by level <= i’; elsif i <= 10000000000 then return ‘select rownum n from ‘|| ‘( select 1 connect by level <= 100000 ),’|| ‘( select 1 connect by level <= i/100000) where rownum <= i’; else return ‘select rownum n from ‘|| ‘( select 1 connect by level <= 100000 ),’|| ‘( select 1 connect by level <= 100000 ),’|| ‘( select 1 connect by level <= i/10000000000) where rownum <= i’; end if; end; /

select * from gen23ai(23);
```
     N
```
Reply
1. Connor McDonald
  
  February 19, 2025 at 12:47 pm
  
  true, but macros are there from 19c onwards, so i wanted to make it version compatible.
  
  Reply
  1. Bob Bryla
    
    February 19, 2025 at 1:16 pm
    
    Of course. I even remember when DUAL was a real table, and sometimes I added a couple of rows to it to see what happened.
    
    Reply