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

Lucas Jellema 8

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:


6 rows selected.

The same goes for strings: ....

select * 
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.  

8 thoughts on “Dummy data without dummy tables – just a little SQL trick with the TABLE operator

  1. Is this possible with SQL2005. I tried this but it did not work..

    I have a small problem. I need to create a view to generate a report. The view has one column called month. The values for this column do not come from any database table. It is coming from the report period (ex:- 01/01/2007 to 31/01/2007) – here month is january. If it is more than one month the values for the other fields must be properly distributed. That is there will be 3 months entries in the case of three months. How can i do this?

  2. How can i do same thing in MS-SQL 2000
    Please give me anser ASAP.

    I can not able to execute
    select * from table ( num_tbl(1,2,3,4,5,6) ) in MS-SQL
    Please tell me what is correct syntax in MS-SQL ?

    Thankx in Advance

  3. Because there no condition after connect by. SQL> select rn from ( 2 select rownum rn 3 from dual 4 connect by rownum select rn from ( 2 select level rn 3 from dual 4 connect by level

  4. mydb> select rownum
    2 from dual
    3 connect by rownum;
    connect by rownum
    FOUT in regel 3:
    .ORA-00920: invalid relational operator

    Verstreken: 00:00:00.04

    hhhm… this doesn’t work here. Odd.

  5. Thanks for all your comments. My reactions:

    – Marco: the query against DUAL would be something like

    I find that awkward.

    – Francois: good point – although I probably would never remember the exact TYPE.

    – Edwin: this would work but basically only for a set of numbers counting from 1 to somewhere. If you want to query strings or a specific set of numeric records, this does not help.

  6. in the sector “useless queries”, my five cents

    select rownum
    from dual
    connect by rownum

  7. Hello,
    You can obtain the same result without creating any type, but using existing ones:
    SELECT * FROM TABLE( sys.dbms_debug_vc2coll (‘John’,’Mike’,’Tobias’) ) ;

  8. Could you explain why using DUAL is an awkward solution for the problem you demonstrated? Using the dummy table DUAL is, as you know, optimized for performance; Has this been changed; Are there equally good solutions now (like the ones which are mentioned above)?



Comments are closed.

Next Post

Oracle APPS 11i Technology Administrator OCP beta program extended

Oracle has “recently” launched the Oracle Applications Technology Certified Professional Administrator (OCP) certification. First, Oracle launched a beta program for this certification, to end on October 31st of this year. However, Oracle now has decided to extend the beta program to the end of January, 2007. This was officially told […]
%d bloggers like this: