generate_series() in Oracle like in PostgreSQL

If you often switch between RDS databases you may be annoyed by the lack of similar API despites the SQL standard.

When I want to quickly generate rows in PostgreSQL or PostgreSQL-compatible like Amazon Aurora or YugabyteDB, I use generate_series() like:

create table demo ( id bigint primary key, value int default 0 );
insert  into demo ( id ) select id from generate_series(1,1000) id;

How to do the same on Oracle?
For a long time, I used:

(select rownum as id from dual connect by level <=1000);

But this is long for more than a thousand rows and is a side effect of the non-standard implementation for recursive queries. Basically, it works but you don't know why.

AS I'm from the lucky generation that has seen these beautiful XML functions coming into the database, I use:

(select to_number(column_value) as id from xmltable('1 to 1000'));

which is more efficient to generate millions of rows. But not better for understanding: writing the range of integers as a character string containing the boomer's XQuery loop with a toclause 😂 and this XMLTABLE() function where I never remember that there's no underscore in it unlike most SQL functions.

When you move to PostgreSQL, generate_series(start,stop) comes as a much better solution. Thanks to SQL Macros, it is easy to emulate in Oracle:

create or replace function generate_series( "start" in int, "stop" in int )
  return varchar2 SQL_MACRO(TABLE) as
begin
 return 'select to_number(column_value) as generate_series from xmltable('''
  ||generate_series."start"||' to '||generate_series."stop"
  ||''')';
end;
/

The idea of SQL Macros is that they generate the SQL string. You write once this ugly xmltable(' ... to ...') and use it as you would use the PostgreSQL one with a start and stop value.