Generating rows magically - the many virtues of DUAL

I was reading one of the last issues of Oracle Magazine when i came across the following tip published by the #1 Oracle Guru, Tom Kyte:

with data
as
(select level n
from dual
connect by level <= 5) select * from data; 

if you don't like to use the WITH syntax in such a simple subquery, you can rewrite it as follows:

select n
from (select level n
from dual
connect by level <= 5);

Edited on May 14, 2007: be sure to check out the comments section, there is a comment from Knut about this sql query. Re-edited on September 3, 2007: It turns out that in Oracle 9i there is something odd with the simplified version of the query (after removing the inline view, so check out the comments for the latest version). This short piece of SQL is especially useful because i was still using the old-fashioned select * from table(function(n)) that is a technique available since Oracle 8i where, in order to spawn an arbitrary number of records on the fly, you must populate an object table with the desired number of elements. Note that this technique is still useful when the data must be retrieved in a less straightforward fashion, as it happens usually with complex business rules. However, thanks to this smart technique that takes advantage of the dual table and a sort of fake hierarchical query, you can spawn a recordset made up of n-rows and without additional objects, that is using less resources, less memory and in a simpler, faster way. Now, i was wondering how nice it would be to have a sort of parametric view, something like:

select n from nrows;

Well, if you don't mind setting up a simple package:

CREATE PACKAGE pkg_nrows
as
n   integer;
function fn_n  return integer;
end;
/

CREATE PACKAGE BODY pkg_nrows
as
function fn_n   return integer
is
begin
return n;
end;
end;
/

CREATE VIEW NROWS ( N ) AS
select level n
from dual
connect by level <= pkg_nrows.fn_n 

You can finally perform the query from view nrows, just after setting the value of the desired n value, that was defined as a packaged global variable.

begin
pkg_nrows.n := 5;
end;
/
select *
from nrows
/

N
-
1
2
3
4
5

And there you go with your freshly made n-tier dual table.

Hope it helps!


PS: as quirk of the day I'd mention the space characters handling inside pre-formatted text of Blogger's HTML editor, i can't make out how it works, it keeps trimming my spaces and line breaks randomly!