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
```