How to extract all the dates within given periods and range In Oracle

In oracle query how to extract all the dates within given periods and range

Consider if there is a table with two column

TABLE TIME_FRAME
------------------------
|FROM       |TO        |
|2013-12-13 |2014-01-06|
|2011-12-05 |2011-12-31|
|2014-01-23 |2014-02-22|
|2011-11-21 |2011-12-17|
........

FROM and TO from each row defines a period of time. Also there can be overlap between the periods (here row 2 and row 4) or cover multiple periods

if give a start_date and end_date as parameters here the requirement is about return all the dates falls within the parameters and also within any of the periods in the columns

for example if start_date is 2013-12-25 and end_date is 2014-02-10 so from above data it should return all dates between

`2013-12-25` and `2014-01-06`
plus
`2014-01-23` and `2014-02-10`

is it possible to create a query for above requirement (not by PL/SQL)?

Answer:

It's possible by creating set of days using LEVEL recursion operator and then filtering this set by comparing it to your table data. Here is the working Oracle SQL query for you:

select day
  from (select to_date('25-DEC-2013', 'dd-mon-yyyy') - 1 + level day
          from dual
        connect by level <= to_date('10-FEB-2014', 'dd-mon-yyyy') -
                   to_date('25-DEC-2013', 'dd-mon-yyyy') + 1)
 where exists
 (select 1 from TIME_FRAME p where day between p.FROM and p.TO);