SQL Query to return N rows from dual

Try something like:

SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;

It's messy, but it'll do the trick.

Edited: Ah - you need to pass in a variable to let you know how high to go...

So how about something like:

SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;

Ok... editing again, now using WITH:

WiTH 
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;

Query without connect by

WITH num(n) as(select 1 from dual union all
select n+1 from num where n <= :num_limit)
select * from num

You could use:

 WHERE ROWNUM <= :NUM

...but the table has to contain row equal or greater to the limit in the bind variable. This link demonstrates various row number generation techniques in Oracle.

Using CONNECT BY, Oracle 10g+:

SELECT LEVEL
  FROM DUAL
CONNECT BY LEVEL <= :NUM

Confirmed by monojohnny that the bind variable can be used. Attempts to run on Oracle 9i, though CONNECT BY syntax is supported results in an ORA-01436 error.

The only thing I'm not 100% on is if the CONNECT BY will accept the limit from the bind variable.

Reference:

• Integer Series Generators - CONNECT BY LEVEL Method

I didn't come up with this answer [ so make sure any votes go the right way!!] , it just my testing notes based on 'OMG Ponies' [who wasn't sure whether the method would work with binding variable] above for reference:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
  2  end;
  3  /

PL/SQL procedure successfully completed.

SQL> select level from dual
  2  connect by level <=:num_rows;

     LEVEL
----------
         1
         2
         3
         4
 ...