SQL Snippets ™: Tutorials for Oracle Developers

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.

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
 

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
 

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