Generating sequential numbers without using user_objects

Sequence number generation in a query has been almost natural in reporting queries. You normally tend to use rownum pseudo column from either your table or if it is a generic query user_objects.

For example:

select rownum from user_objects where rownum <= 100;

The output will be:

ROWNUM

---------------

1

2

3

.

.

100

This is based on the assumption that there are more number of objects in user_objects view so as to return a high number. The more robust way to get an output without using such a large view was not literally possible in pre-9i era. Starting 9i Oracle has come up with a beautiful solution. You can use DUAL table in conjunction with CONNECT BY clause to come up with such a result.

Example:

select level from dual connect by level <=100;

This also will generate the same output generated in our first case.