-- SQL Server version
-- Generate a calendar table containing the months from start to end date
WITH cte_months 
AS 
( 
    SELECT cast('2009-10-01' as datetime) AS aMonth     -- parameter
    UNION ALL 
    SELECT DATEADD(mm, 1, aMonth) 
    FROM cte_months
    WHERE DATEADD(mm, 1, aMonth) < '2010-10-01'        -- parameter
) select * from cte_months

2009-10-01 00:00:00.0
2009-11-01 00:00:00.0
2009-12-01 00:00:00.0
2010-01-01 00:00:00.0
2010-02-01 00:00:00.0
2010-03-01 00:00:00.0
2010-04-01 00:00:00.0
2010-05-01 00:00:00.0
2010-06-01 00:00:00.0
2010-07-01 00:00:00.0
2010-08-01 00:00:00.0
2010-09-01 00:00:00.0

-- Generate a calendar table containing the months from start to end date
WITH cte_months
AS 
( 
    SELECT to_date('2009-10-01', 'YYYY-MM-DD') AS aMonth from dual    
    UNION ALL 
    SELECT add_months(aMonth, 1) FROM cte_months
    WHERE add_months(aMonth, 1) < to_date('2010-10-01', 'YYYY-MM-DD')
) select * from cte_months

WITH cte_months
        AS (    SELECT ADD_MONTHS (TO_DATE ('2009-10-01', 'YYYY-MM-DD'),
                                   (LEVEL - 1))
                          AS aMonth
                  FROM DUAL
            CONNECT BY LEVEL <= 12)
SELECT *
  FROM cte_months;

SELECT add_months(date '2001-01-01', rownum - 1) mon
FROM all_objects ao
WHERE rownum <= 12