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:
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.
CONNECT BY, Oracle 10g+:
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= :NUM
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.