Dummy data without dummy tables – just a little SQL trick with the TABLE operator

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.  

8 Comments

  1. Rumesh October 17, 2007
  2. chirag December 7, 2006
  3. Janusz Perek November 30, 2006
  4. Patrick Sinke November 29, 2006
  5. Lucas Jellema November 27, 2006
  6. Edwin van Meerendonk November 27, 2006
  7. Francois November 27, 2006
  8. Marco Gralike November 27, 2006