Harish Harish - 4 months ago 14
SQL Question

SQL Query to return N rows from dual

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?

Answer

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:

Comments