Learning is not a spectator sport

Throughout my long history of working with the Oracle Database, there are some things that often just take for granted, on the assumption that they have always been present.

But there was a time in the Oracle landscape where we used to have a whole bunch of weird, complicated solutions to the task of synthesising rows out of nothing in the Oracle database.

And then, in February 2005, a fellow named Mikito, who regularly sparred with Tom Kyte over SQL techniques on asktom.oracle.com, suggested this little gem tucked well down the page as a quick blink-and-you’ll-miss-it comment.

And voila! Just about everyone in the Oracle community has been using this technique ever since. There’s been some modifications and improvements to the technique over time, but the fundamentals remained the same.

As databases got bigger, Tanel Poder discovered that pushing the technique to higher numbers could lead to PGA issues:

and came up with the simple but very clever solution of a cartesian product to keep each individual hierarchy CONNECT BY count to a smaller number.

Some years later, Franck Pachot brought his Postgres influence into the Oracle sphere with a SQL macro version to create a Postgres-inspired function called GENERATE_SERIES as a wrapper around the XMLTABLE technique for synthesizing rows.

If you’ve never seen this solution, here’s a quick demo of it in action

I was never a huge fan of the XMLTABLE technique because of the need to TO_NUMBER every output, and whilst wrapping the complexity in a SQL Macro is a cool idea, it collapses if you’re using bind variables to invoke it:

So to celebrate 21 years of this cool technique, here’s my own version, which takes Franck’s SQL Macro idea, combines it Tanel’s solution and modifies it to be used within a SQL Macro.

SQL> create or replace
  2  function gen_rows(p_start int, p_stop int) return varchar2 sql_macro is
  3  begin
  4    return
  5        'select rownum-1+p_start r
  6         from ( select 1 from dual connect by level <= 1e4),
  7              ( select 1 from dual connect by level <= case when p_stop - p_start + 1 > 1e4 then 1e4 else 1 end),
  8              ( select 1 from dual connect by level <= case when p_stop - p_start + 1 > 1e8 then 1e4 else 1 end),
  9              ( select 1 from dual connect by level <= case when p_stop - p_start + 1 > 1e12 then 1e4 else 1 end)
 10         where rownum <= p_stop - p_start + 1';
 11  end;
 12  /

Function created.

SQL> select * from gen_rows(1,5);

         R
----------
         1
         2
         3
         4
         5

SQL> variable x number
SQL> variable y number
SQL> exec :x := 10; :y := 20;

PL/SQL procedure successfully completed.

SQL> select * from gen_rows(:x,:y);

         R
----------
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20

11 rows selected.

But credit where credit is due. Thanks Mikito for this SQL contribution that we’ve all benefitted from