## Integer Series Generators

### Pipelined Function Method

This tutorial demonstrates how to generate a series of integers using a Pipelined Function. Other techniques are discussed in the tutorials listed in the menu to the left.

#### Prerequisites

This solution requires a nested table type or varry type. We will use one called INTEGER_TABLE_TYPE created in the Setup topic for this section. If you do not have privileges to create a type like this see Setup - Note 1.

```desc integer_table_type
integer_table_type TABLE OF NUMBER(38)

```

You will also need the following custom database function.

```create function integer_series
(
p_lower_bound in number,
p_upper_bound in number
)
return integer_table_type
pipelined
as
begin

for i in p_lower_bound .. p_upper_bound
loop
pipe row(i);
end loop;

return;

end;
/

```

#### The Solution

Now that we have our prerequisites in place, here is how you use the INTEGER_SERIES function.

```select *
from   table( integer_series(-5,7) ) ;
```
```COLUMN_VALUE
------------
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
```

We can apply this technique to our day of the week scenario with this query.

```select
i.column_value as day_of_week ,
t.val
from
table( integer_series(0,6) ) i
left outer join t
on ( i.column_value = t.day_of_week )
order by
i.column_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-11824.html]SQL Snippets: Integer Series Generators - Pipelined Function Method[/url]