## Integer Series Generators

### Type Constructor Expression Method

This tutorial demonstrates how to generate a series or set of integers using Type Constructor Expressions for collection types. 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)

```

#### The Solution

If you need a manageable number of integers, like 10 or 20, you can use a simple query like this one.

```select column_value
from   table( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) ;
```
```COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
```

This method is unique from the others in this section in that it lends itself well to creating sets of non-sequential integers as well as sequential series.

```select column_value
from   table( integer_table_type( 1,1,4,4,4,8,10 ) ) ;
```
```COLUMN_VALUE
------------
1
1
4
4
4
8
10
```

Applying the technique to our day of the week scenario yields this query.

```select
i.column_value as day_of_week ,
t.val
from
table( integer_table_type( 0,1,2,3,4,5,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
```

If you require more integers than you care to list in a type constructor expression see the Type Constructor + Cartesian Product tutorial for a variation of this technique.

#### Gotchas

If we specify more than 999 arguments in a type constructor it will generate a ORA-00939: too many arguments for function error (as tested in Oracle 10g).

#### 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-11832.html]SQL Snippets: Integer Series Generators - Type Constructor Expression Method[/url]