MacLochlainns Weblog

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'MOCK_CALENDAR') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE mock_calendar
( short_month  VARCHAR2(3)
, long_month   VARCHAR2(9)
, start_date   DATE
, end_date     DATE );
 
-- Seed the table with 10 years of data.
DECLARE
  -- Create local collection data types.
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  long_month  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');
 
  -- Declare base dates.
  start_date DATE := '01-JAN-79';
  end_date   DATE := '31-JAN-79';
 
  -- Declare years.
  years      NUMBER := 20;
 
BEGIN
 
  -- Loop through years and months.
  FOR i IN 1..years LOOP
    FOR j IN 1..short_month.COUNT LOOP
      INSERT INTO mock_calendar VALUES
      ( short_month(j)
      , long_month(j)
      , add_months(start_date,(j-1)+(12*(i-1)))
      , add_months(end_date,(j-1)+(12*(i-1))));
    END LOOP;
  END LOOP;
 
END;
/
 
-- Format set break for output.
SET PAGESIZE 16
 
-- Format column output.
COL short_month FORMAT A5 HEADING "Short|Month"
COL long_month  FORMAT A9 HEADING "Long|Month"
COL start_date  FORMAT A9 HEADING "Start|Date"
COL end_date    FORMAT A9 HEADING "End|Date" 
 
SELECT * FROM mock_calendar;

Short Long      Start     End
Month Month     Date      Date
----- --------- --------- ---------
JAN   January   01-JAN-79 31-JAN-79
FEB   February  01-FEB-79 28-FEB-79
MAR   March     01-MAR-79 31-MAR-79
APR   April     01-APR-79 30-APR-79
MAY   May       01-MAY-79 31-MAY-79
JUN   June      01-JUN-79 30-JUN-79
JUL   July      01-JUL-79 31-JUL-79
AUG   August    01-AUG-79 31-AUG-79
SEP   September 01-SEP-79 30-SEP-79
OCT   October   01-OCT-79 31-OCT-79
NOV   November  01-NOV-79 30-NOV-79
DEC   December  01-DEC-79 31-DEC-79