Dummy data without extra tables
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 Create the type which is an array of integers
create or replace type int_arr as table of integer;
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
create or replace function get_ints (
how_many in number
) return int_arr
v_int_arr int_arr := int_arr();
for r1 in 1..how_many loop
v_int_arr(r1) := r1;
Once we have the function, we can call the function (from SQL) and convert the returned array into a cursor:
the(select cast(get_ints(7) as int_arr) from dual)
10 rows selected.
A more useful demonstration, including a join
rem Create and populate a demo table with 2 date columns
drop table jpl_demo;
create table jpl_demo(
insert into jpl_demo values('XX',trunc(sysdate), trunc(sysdate)+4);
insert into jpl_demo values('YY',trunc(sysdate), trunc(sysdate)+3);
rem Now use the get_ints trick to expand each of the
rem date ranges into a list of consecutive dates
break on v1 skip 1
d1 + column_value - 1
the(select cast(get_ints(100) as int_arr) from dual) nlist
nlist.column_value <= d2 - d1 + 1
9 rows selected.
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.