CODEWITHSUNDEEP
sqloracle-database
hiway
hiway

Reputation: 3976

how to generate consecutive records with a given number?

in oracle, is there a built-in function to produce consecutive records with a given number? For example, the number is 100, so that you can generate a result-set with 100 records whose values are 1, 2, 3, 4...100, like the following:

1
2
3
4
...
100

I know store procedure can do this, and I want to know if there are other ways just using sql statements?

Upvotes: 1

Views: 127

Answers (4)

Nick Krasnov
Nick Krasnov

Reputation: 27261

Here is another approach, using model clause. (Oracle 10g and higher). 

SQL> select x
  2    from dual
  3    model
  4    dimension by (0 as z)
  5    measures (0 as x)
  6    rules iterate(101) (
  7      x[iteration_number] =  iteration_number
  8    )
  9  ;

         X
----------
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

        ...

        100

Upvotes: 2

Ben
Ben

Reputation: 52903

It's traditional to use a hierarchical query:

 select level
   from dual
connect by level <= 100

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

select level 
from dual 
connect by level <= 100

Upvotes: 4

TechDo
TechDo

Reputation: 18659

Please try using CTE:

WITH numbers(n) AS
(
SELECT 1 FROM dual
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

Upvotes: 1

Related Questions