From time to time people raise the problem of generating 'padding' to fill the gaps between two items of real data; for instance if a single row in a table contains the values
start_date = '01-Jun-99' , end_date = '05-Jun-99'
how can you list the dates from 1st June to 5th June ?
The standard way in version 7 is to create a table with a single numeric column, and populate it with a list of consecutive values. This note demonstrates an Oracle 8 alternative that uses the Object Option (which I believe is included as a standard part of the Enterprise Edition in Oracle 8.1, but is separately licensed in Oracle 8.0).
The mechanism is based on a user-defined array type, a function to populate an instance of that array, and the CAST method to turn the array into a cursor.
Creating the Infrastructure:
rem rem Create the type which is an array of integers rem
create or replace type int_arr as table of integer; / rem rem Create a function which takes an integer input, and rem returns an array of integers of that size, with each rem element of the array set to the array index rem create or replace function get_ints ( how_many in number ) return int_arr as v_int_arr int_arr := int_arr(); begin for r1 in 1..how_many loop v_int_arr.extend; v_int_arr(r1) := r1; end loop; return v_int_arr; end; /
Basic Use:
Once we have the function, we can call the function (from SQL) and convert the returned array into a cursor:
select * from the(select cast(get_ints(7) as int_arr) from dual) ;
COLUMN_VALUE
------------
1
2
3
4
5
6
7
10 rows selected.
A more useful demonstration, including a join
rem
rem Create and populate a demo table with 2 date columns
rem
drop table jpl_demo;
create table jpl_demo(
v1 varchar2(5),
d1 date,
d2 date
);
insert into jpl_demo values('XX',trunc(sysdate), trunc(sysdate)+4);
insert into jpl_demo values('YY',trunc(sysdate), trunc(sysdate)+3);
rem
rem Now use the get_ints trick to expand each of the
rem date ranges into a list of consecutive dates
rem
break on v1 skip 1
select
v1,
d1 + column_value - 1
from
jpl_demo,
the(select cast(get_ints(100) as int_arr) from dual) nlist
where
nlist.column_value <= d2 - d1 + 1
order by
v1,
column_value
;
V1 D1+COLUMN
----- ---------
XX 26-JUN-99
27-JUN-99
28-JUN-99
29-JUN-99
30-JUN-99
YY 26-JUN-99
27-JUN-99
28-JUN-99
29-JUN-99
9 rows selected.
Warning:
There are two reasons for using the CAST method in this sort of way. First it is convenient, secondly it may allow you to reduce physical I/O in exchange for memory and CPU costs.
Treat the method with a little caution, it is probably not sensible to stress it too violently without very careful plannning. Having said that, I have run a couple of tests to return 250,000 rows (or integers only) using the CAST. On a small PC this took only a few seconds. The memory cost is interesting: on 8.0.4, each element of the object array required about 135 bytes; the equivalent cost on 8.1.5 was 85 bytes. (You may recall that one of the announced features of 8.1 was the improved packing of object types.)
A future article will outline the overheads of storing data in objects. Another article will describe an enhancement of the technique above to use pl/sql arrays instead of (old-style - i.e. pre-8.1) temporary tables.