## Integer Series Generators

### Type Constructor + Cartesian Product Method

This tutorial demonstrates how to generate a series of integers using Type Constructor Expressions for collection types and Cartesian Products. 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 require a large number of integers then listing them all in a type constructor expression, like the solutions in the Type Constructor Expression Method tutorial, may be difficult or impossible. In this case you can use a Cartesian product with your type constructor expressions to generate a large number of rows with a small amount of code. Here are some examples.

 This query returns 9 rows (3x3). ```select rownum from table( integer_table_type( 1,2,3 ) ) i1, table( integer_table_type( 1,2,3 ) ) i2 ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8 9  ``` This query returns 12 rows (3x4). ```select rownum from table( integer_table_type( 1,2,3 ) ) i1, table( integer_table_type( 1,2,3,4 ) ) i2 ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12  ``` A query like this can return up to 10,000 rows (10^4), though we won't prove this by diplaying them all here. Listing 15 of them should suffice. ```with i as ( select * from table ( integer_table_type( 1,2,3,4,5,6,7,8,9,10 ) ) ) select rownum from i,i,i,i where rownum <= 15 ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15  ```

#### How Cartesian Products Work

When you do not specify a join between tables Oracle combines each row of one table with each row in the other to produce every possible row combination. This produces a result set with

`(number of rows in Table 1) x (number of rows in Table 2) `

rows in it. The following query illustrates this.

```select
rownum ,
i1.column_value i1_column_value,
i2.column_value i2_column_value
from
table( integer_table_type( 1,2      ) ) i1,
table( integer_table_type( 10,20,30 ) ) i2
;
```
```    ROWNUM I1_COLUMN_VALUE I2_COLUMN_VALUE
---------- --------------- ---------------
1               1              10
2               1              20
3               1              30
4               2              10
5               2              20
6               2              30
```

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