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

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 * <br />from   table ( num_tbl(1,2,3,4,5,6) )&nbsp;

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') )&nbsp;

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>/&nbsp;</p>

and

create type string_table as table of varchar2(2000)&nbsp;<br /><p>/&nbsp;</p>

Of course the Table operator could call a PL/SQL function that can generate more complex records according to far more complex algoritms.  

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

8 Comments

  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
    mydb>

    hhhm… this doesn’t work here. Odd.

  5. Thanks for all your comments. My reactions:

    - Marco: the query against DUAL would be something like
    SELECT ‘Tobias’ FROM DUAL
    UNION
    SELECT ‘Mike’ FROM DUAL
    UNION
    SELECT ‘John’ FROM DUAL
    etc.

    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. Edwin van Meerendonk on

    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)?

    Grz

    Marco