## Integer Series Generators

### Integer Table Method

This tutorial demonstrates how to generate a series of integers using a generic integer table. Other techniques are discussed in the topics listed in the menu to the left.

One of the most straightforward ways to generate a series of integers is by adding a generic integer table to your application. You can create such a table like this.

```create table integers
( integer_value integer primary key )
organization index
;

Table created.

```

Since this table only has a single indexed column we specified `organization index` to make this an Index-Organized table and save storage space.

To load the table a simple loop like the following will do the trick.

```begin
for i in -5 .. 10 loop
insert into integers values ( i );
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

```

We used -5 and 10 as the limits of our series in this example. In practice you would choose limits that anticipate the smallest and largest integers you will ever need.

```select * from integers ;
```
```INTEGER_VALUE
-------------
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
10
```

Later, when you need a specific series of integers you can use the INTEGERS table like this.

```select
i.integer_value as day_of_week ,
t.val
from
integers i
left outer join t on
( i.integer_value = t.day_of_week )
where
i.integer_value between 0 and 6
order by
i.integer_value
;
```
```DAY_OF_WEEK        VAL
----------- ----------
0
1        100
2
3        300
4        400
5        500
6
```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11820.html]SQL Snippets: Integer Series Generators - Integer Table Method[/url]