While writing useless queries for solving exotic challenges, I realized that the use of dummy tables or unions of selects against DUAL for producing a few data-rows is a awkard solution to a problem that has a much more elegant one. Using a database type and the TABLE operator we have a very compact way at our disposal for generating records on the fly.
This means that using a simple statement like:
from table ( num_tbl(1,2,3,4,5,6) )
we can generate rows with numeric values:
6 rows selected.
The same goes for strings:
from table( string_table('John','Mike','Tobias') )
with the following result:
The only set up we need to do is create the database TYPE that will be created and queried from on the fly:
create type num_tbl as table of number
create type string_table as table of varchar2(2000)
Of course the Table operator could call a PL/SQL function that can generate more complex records according to far more complex algoritms.