Data warehouse ----Time dimension
-----Create table

CREATE TABLE DIM_DATE
(
  DATE_ID      INTEGER,
  YEAR         INTEGER,
  MONTH        INTEGER,
  QUARTER      INTEGER,
  WEEK         INTEGER,
  DAY          INTEGER,
  CREATE_DATE  DATE
)

-----Create a procedure to fill the data
CREATE OR REPLACE PROCEDURE fill_dim_date (start_date IN char,end_date  IN char)
AS
   v_counter   number := 0;
   v_max       number := 0;
BEGIN
   EXECUTE IMMEDIATE 'truncate table dim_date';

   v_max :=
      TO_NUMBER(TO_DATE (end_date, 'yyyy-mm-dd')
                - TO_DATE (start_date, 'yyyy-mm-dd'));

   LOOP
      INSERT INTO dim_date (
                                 DATE_ID,
                                 YEAR,
                                 MONTH,
                                 DAY,
                                 WEEK,
                                 CREATE_DATE,
                                 QUARTER
                 )
        VALUES   (
                     to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'yyyymmdd')),
                     to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'yyyy')),
                     to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'mm')),
                     to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'dd')),
                     to_number(TO_CHAR (TO_DATE (start_date, 'yyyy-mm-dd') + v_counter,'w')),
                &n