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).