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

Lucas Jellema 8
0 0
Read Time:57 Second

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.  

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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

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: