How to create Calendar Table in Oracle

Question

How to create Calendar Table Oracle. like i have table of holidays

Id  FromDate    ToDate  Description
1   1-Feb-11    3-Feb-11    Maintance
3   2-Sep-09    5-Sep-09    Eid Holiday Fine Block
4   3-Dec-09    4-Dec-09    Due to System Problem
5   4-Dec-07    04-Dec-07   National Day

I want to make CTE from this table from minimum fromdate and maximum todate.

Expecting table like.

Date        HolidayName

11-Feb-11   Maintance
12-Feb-11   Maintance
13-Feb-11   Maintance
02-Sep-09   Eid Holiday
03-Sep-09   Eid Holiday
04-Sep-09   Eid Holiday
05-Sep-09   Eid Holiday

Answer 1

connect by level clause may be used :

 select FromDate+level-1 "Date", Description "HolidayName"
   from holiday 
connect by level <=  ToDate - FromDate + 1
  group by FromDate+level-1, Description
  order by 1;

demo

Answer 2

IN oracle 11g 2 You can use CTE Recursive

like this.

WITH CTE(from_date, i, datediff,description) AS
(
  SELECT from_date, 1 AS i, trunc(to_date - from_date) as datediff,description
  FROM t
  UNION ALL
  SELECT from_date, i + 1, datediff,description
  FROM CTE
  WHERE i <= datediff
)
, dates as (select i, from_date + i - 1 as "Date",description from CTE)
SELECT "Date",description "HolidayName"
FROM dates
ORDER BY description desc

Sqlfiddle:http://sqlfiddle.com/#!4/ac34c/7