dummy data without dummy tables - just a little sql trick with the TABLE operator

출처 : http://technology.amis.nl/blog/?p=1413

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 * 

from   table ( num_tbl(1,2,3,4,5,6) ) 

we can generate rows with numeric values:

COLUMN_VALUE

————

           1

           2

           3

           4

           5

           6


6 rows selected. 

The same goes for strings: 

select * 

from   table( string_table(’John’,'Mike’,'Tobias’) ) 

with the following result:

COLUMN_VALUE

———————-

John

Mike

Tobias

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

/ 

and

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.