SQL Query to return N rows from dual

Question

I want to write a SQL query which accepts a bind variable (say :NUM) and its output consists of one column & :NUM number of rows, each row having its row number. i.e. if we pass :NUM as 7, the output should be:

VAL
====
1
2
3
4
5
6
7

There shouldn't be any actual DB tables in query and no PL/SQL code should be used. i.e. only dual should be used in the query

Is there any way to achieve this?

Solution 1

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

Solution 2

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
;

Solution 3

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
 ...

Solution 4

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

View more solutions

Comments

[Rob Farley]

...then you continue the pattern as required. You could use a recursive CTE, but I'm not sure what the Oracle syntax is for that. You could also use a row_number style approach.

[OMG Ponies]

@Rob: Oracle support for recursive WITH clauses starts 11g iirc.

[Mark Byers]

I think from the mention of 'PL/SQL' and the 'dual' table that he wants a solution for Oracle.

[Mark Byers]

+1 This method is also suggested here: adp-gmbh.ch/ora/sql/examples/generate_rows.html

[Kobi]

This gives me an error for any value above one. SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 20 : ORA-01436

[Kobi]

Good point, it worked on a a newer server (10g), but not on 9.0.1.5.0. Other options from your link work well, though.

[monojohnny]

With regard to this working with a bind variable: I tried it and it does work (used 10.2.0.1.0): I'll post an answer to this effect for reference.

[Rob Farley]

Yeah, but I'm not sure if it's the same as in MS-SQL.

[Jeffrey Kemp]

DUAL is the best way to perform this kind of thing - in 10g+ Oracle gives FAST DUAL which involves zero block reads. DUAL will almost always outperform home-made tables.

[paxdiablo]

No doubt, but we don't use just Oracle. The solution we have is vendor-agnostic and performs more than fast enough.