-- The code below can be used to create annual calendar using 
-- Oracle date functions.  The only input needed is the four digit
-- year (2016 in the reference output).  

-- The fixed day specific holidays such as January 1st, July 4th,
-- August 15th, December 25th, etc. can be easily computed by 
-- DECODE(TO_CHAR(day_of_year_date),'mmdd'),'0101','Y','0704',...)
-- as shown below.

-- Others holidays - such as first Monday/last Monday can be 
-- calculated using date functions.  Those such as third Monday, 
-- etc. can be calculated using analytic functions
-- SUM() OVER (PARTITION BY ... ORDER BY ...) and 
-- COUNT() OVER (PARTITION BY ...) is used to get
-- the last Monday of May, which is Memorial day (USA)
-- as shown below. 

DECLARE
 CURSOR c_ac(i_yyyy IN NUMBER) IS
  WITH q_fdy AS
   (SELECT 
     TRUNC(TO_DATE(i_yyyy,'yyyy'),'yyyy') first_day
    FROM DUAL),
  q_days AS
   (SELECT 
     first_day,
     ADD_MONTHS(first_day,12)-first_day days_in_yr
    FROM q_fdy),
  q_day AS
   (SELECT
     LPAD(LEVEL,3,'0') day_of_yr,
     TO_CHAR(first_day+LEVEL-1,'mm/dd/yyyy')  as cal_day,
     TO_CHAR(first_day+LEVEL-1,'dd')  as day_of_month,
     TO_CHAR(first_day+LEVEL-1,'Dy') day_of_week,
     TO_CHAR(first_day+LEVEL-1,'MON') month_desc,
     DECODE(TO_CHAR(first_day+LEVEL-1,'Dy'),
                       'Sat','N','Sun','N','Y')  week_day,
     SUM(DECODE(TO_CHAR(first_day+LEVEL-1,'Dy'),'Mon',1,NULL))
      OVER (PARTITION BY
              DECODE(TO_CHAR(first_day+LEVEL-1,'Dy'),'Mon',1,NULL), 
              TO_CHAR(first_day+LEVEL-1,'MON')
            ORDER BY first_day+LEVEL-1) mon_day,
     DECODE(TO_CHAR(first_day+LEVEL-1,'mmdd'),
      '0101','New Year','0704','Independence Day',
      '0815','Independence Day','1225','Christmas', NULL) holiday
    FROM  q_days
    CONNECT BY LEVEL <= days_in_yr
    ORDER BY 2),
  q_final AS
  (SELECT 
    day_of_yr,
    cal_day,      week_day,
    day_of_week,  day_of_month,
    month_desc,
    COUNT(mon_day) OVER(PARTITION BY month_desc) mon_cnt,
    mon_day,
    holiday
  FROM q_day)
  SELECT 
    day_of_yr,
    cal_day,      week_day,
    day_of_week,  day_of_month,
    DECODE(mon_day,NULL,NULL,'Monday ['||mon_day||']')
      mon_day,
    CASE WHEN month_desc = 'JAN' AND
              mon_day = 3 THEN 'ML King Day'
         WHEN month_desc = 'FEB' AND
              mon_day = 3 THEN 'President||CHR(39)||s Day'
         WHEN month_desc = 'MAY' AND
              mon_cnt = mon_day THEN 'Memorial Day'
         WHEN month_desc = 'SEP' AND
              mon_day = 1 THEN 'Labor Day'
         ELSE  holiday
    END holidays
  FROM q_final
  ORDER BY day_of_yr;

  v_year      NUMBER(4) := &v_yyyy;
BEGIN
 FOR cal_rec  IN  c_ac(v_year) LOOP
   DBMS_OUTPUT.PUT_LINE('Day ['||cal_rec.day_of_yr||'] -> '||
     cal_rec.day_of_month||' -> '||
     cal_rec.day_of_week||',  '||
     cal_rec.cal_day||',  '||
     cal_rec.week_day||'  '||
     cal_rec.holidays||'  '||
     cal_rec.mon_day
    );

 END LOOP;
END;
/

=> Gregorian Calendar Setup Output