Create a virtual table with numbers

Sometimes I need a virtual table with increasing number.

So a table with contents:

1
2
3
4
5

For this purpose a number of implementations are used on projects I worked on.

 

For example:

* A real table with increasing number.

* select rownum on all_objects where rownum < 1000;

* A pipelined function that returns numbers from a for loop

* select rownum
from (SELECT 1 FROM dual GROUP BY CUBE(1,2,3,4,5,6) )
where rownum < 50;

But today a post on the Amis weblog (http://technology.amis.nl/blog/?p=1751) gave me a more elegant solution using connect by level.

SELECT level
FROM dual
CONNECT BY LEVEL <= 365;

I will use that the next time.

Leave a Reply

Your email address will not be published. Required fields are marked *