Weblog for the AMIS Technology corner
About the author : Lucas Jellema
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, ADF, Java in various shapes and forms and many other things. Author of ...
More by Lucas Jellema
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----------------------JohnMikeTobias
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.
27/11/2006 - 1:24 pm
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
27/11/2006 - 1:25 pm
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’) ) ;
27/11/2006 - 4:06 pm
in the sector “useless queries”, my five cents
select rownum
from dual
connect by rownum
27/11/2006 - 4:56 pm
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.
29/11/2006 - 7:16 pm
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.
30/11/2006 - 7:30 pm
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
7/12/2006 - 6:44 pm
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
17/10/2007 - 12:00 pm
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?