This code will generate a list of integers from 1 to 50
10g and above

 

SELECT  mynum
  FROM (SELECT 1 mynum FROM dual)
  MODEL
  DIMENSION BY (mynum )
  MEASURES ('mynote' AS note)
  RULES  (note[FOR mynum FROM 1 TO 50 INCREMENT 1] = '' )
ORDER BY mynum;


This code will also generate a list of integers from 1 to 50;
8i and above

SELECT LEVEL num
FROM   Dual
CONNECT BY LEVEL <= 50;
If you want to use either of these examples, make sure you test the performance. The MODEL example may be quite costly when generating very long sequences. The only advantage to the MODEL example is that it is ANSII compliant.  The following example illustrates that point.

 Connected.
SQL> alter session set FLAGGER = FULL;
Session altered.
SQL> SELECT  mynum
2            FROM (SELECT 1 mynum FROM dual)
3           MODEL
4       DIMENSION BY (mynum )
5        MEASURES ('mynote' as note)
6           RULES  (note[FOR mynum FROM 1 to 5 INCREMENT 1] = '' )
7           ORDER BY mynum;
MYNUM
———-
1
2
3
4
5
SQL> SELECT LEVEL num
2  FROM   Dual
3  CONNECT BY LEVEL <= 5;
CONNECT BY LEVEL <= 5
*
ERROR at line 3:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 4, column 1:
PLS-01425: Connect-by clause