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:
select * <br />from table ( num_tbl(1,2,3,4,5,6) )
we can generate rows with numeric values:
COLUMN_VALUE<br />------------<br /> 1<br /> 2<br /> 3<br /> 4<br /> 5<br /> 6<br /><br />6 rows selected. <br />
The same goes for strings:
select * <br />from table( string_table('John','Mike','Tobias') )
with the following result:
COLUMN_VALUE<br />----------------------<br />John<br />Mike<br />Tobias<br />
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<br /><p>/ </p>
create type string_table as table of varchar2(2000) <br /><p>/ </p>
Of course the Table operator could call a PL/SQL function that can generate more complex records according to far more complex algoritms.