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
Learning is not a spectator sport 
Leave a reply to Bob Bryla Cancel reply