JL Computer Consultancy

Dummy data without extra tables

June 1999


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.


Back to Main Index of Topics