Multi-Rows from DUAL

select * from 

(select * from dual connect by level <= 1000)

Be careful when using this approach. There are a few things that can trip you up.

1. If you use a bind variable in place of the value 1000 and your bind variable value can ever be 0 the query will not work as expected.

2. People have reported bugs with these types of queries in various Oracle versions.

3. There is some debate about whether the syntax is legal and whether it will continue to work in future versions.

Given these risks you may want to consider using this alternative query instead (assuming you are running on 10g).

select integer_value
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

As an added bonus, the MODEL query runs faster than the CONNECT BY LEVEL query.

You can find more details (including a fix for issue #1) at

http://www.sqlsnippets.com/en/topic-11821.html (SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method)

and

http://www.sqlsnippets.com/en/topic-11979.html (SQL Snippets: Integer Series Generators - MODEL Method).

Hope this helps.

MODEL Evil???? Oh no, no, no. Au contraire mon ami. MODEL is the best thing since sliced bread. It's my new favourite feature. It slices, it dices, it loops, it straggs ... it does all sorts of neat things. It's like having PL/SQL in your queries without the context switches.

I think people may not appreciate MODEL's usefulness because of the learning curve associated with it. MODEL packs a lot of functionality into one little clause so it's hard to get your head around it unless you learn it the right way. That's one of the reasons I wrote a really easy tutorial series on MODEL that goes through each component one step at a time. Check it out at http://www.sqlsnippets.com/en/topic-11663.html and then tell me if you still think it's evil or simply a misunderstood little feature. :-)