Table of Dates

Use a PL/SQL collection TYPE to generate a date list as a database table which you can then use as time series table in your data warehouse.


1. Create a DATE_TABLE type.

SQL> create or replace TYPE "DATE_TABLE" AS TABLE OF DATE;

2. Create the DATE_RANGE function to manipulate (load) the above type.

.

CREATE OR REPLACE FUNCTION date_range(from_dt IN DATE, to_dt IN DATE) 
RETURN date_table AS
a_date_table date_table := date_table();
cur_dt DATE := from_dt;
BEGIN
WHILE cur_dt <= to_dt
LOOP
a_date_table.extend;
a_date_table(a_date_table.COUNT) := cur_dt;
cur_dt := cur_dt + 1;
END LOOP;
RETURN a_date_table;
END date_range;

more about this function here:

http://www.adp-gmbh.ch/ora/sql/table_cast.html

3. Use the TYPE as a date table which you can then join with other tables.

SELECT column_value DAYS
FROM TABLE(CAST(date_range('18-FEB-1981', '25-FEB-1981') AS date_table))

/

DAYS
-------------------------
18-FEB-81
19-FEB-81
20-FEB-81
21-FEB-81
22-FEB-81
23-FEB-81
24-FEB-81
25-FEB-81

Then use the "Date Table" to join it with SCOTT.EMP and get the employees which were hired during the period

SELECT d.DAYS,e.ename,e.hiredate
FROM emp e,

(SELECT column_value DAYS
FROM TABLE(CAST(date_range('18-FEB-1981',    '25-FEB-1981') AS
date_table)))  d

WHERE d.DAYS = e.hiredate(+)
/

DAYS                      ENAME      HIREDATE
------------------------- ---------- -------------------------
18-FEB-81
19-FEB-81
20-FEB-81                 ALLEN      20-FEB-81
21-FEB-81
22-FEB-81                 WARD       22-FEB-81
23-FEB-81
24-FEB-81
25-FEB-81