SQL Snippets ™: Tutorials for Oracle Developers

This tutorial demonstrates how to generate a series of integers using a Pipelined Function. 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)

 

You will also need the following custom database function.

create function integer_series
(
  p_lower_bound in number,
  p_upper_bound in number
)
  return integer_table_type
  pipelined
as
begin

  for i in p_lower_bound .. p_upper_bound
  loop
    pipe row(i);
  end loop;

  return;

end;
/

 

The Solution

Now that we have our prerequisites in place, here is how you use the INTEGER_SERIES function.

select *
from   table( integer_series(-5,7) ) ;
 

COLUMN_VALUE
------------
          -5
          -4
          -3
          -2
          -1
           0
           1
           2
           3
           4
           5
           6
           7
 

We can apply this technique to our day of the week scenario with this query.

select
  i.column_value as day_of_week ,
  t.val
from
  table( integer_series(0,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