Question: How to Create a `View` with all days in year. `view` should fill with dates from JAN-01 to Dec-31. How can I do this in Oracle ?

If current year have 365 days,`view` should have 365 rows with dates. if current year have 366 days,`view` should have 366 rows with dates. I want the `view` to have a single column of type `DATE`.

Answer: 
This simple view will do it:

```
create or replace view year_days as
select trunc(sysdate, 'YYYY') + (level-1) as the_day
from dual
connect by level <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'))
/

```

Like this:

```
SQL> select * from year_days;

THE_DAY
---------
01-JAN-11
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
10-JAN-11
11-JAN-11

...

20-DEC-11
21-DEC-11
22-DEC-11
23-DEC-11
24-DEC-11
25-DEC-11
26-DEC-11
27-DEC-11
28-DEC-11
29-DEC-11
30-DEC-11
31-DEC-11

365 rows selected.

SQL> 

```

The date is generated by applying several Oracle date functions:

* `trunc(sysdate, 'yyyy')` gives us the first of January for the current year
* `add_months(x, 11)` gives us the first of December
* `last_day(x)` gives us the thirty-first of December
* `to_char(x, 'DDD')` gives us the number of the thirty-first of December, 365 this year and 366 next.
* This last figure provides the upper bound for the row generator `CONNECT BY LEVEL <= X`
you can use piplined table, it should be something like this:

```
create or replace type year_date_typ as object (v_day date);
create or replace type year_date_tab as table of year_date_typ;

CREATE OR REPLACE FUNCTION get_dates(year IN VARCHAR2) RETURN year_date_tab PIPELINED IS
v_start_date date := to_date('0101' || year, 'ddmmyyyy');
res year_date_typ := year_date_typ(null);
v_days_in_year integer := 365;
BEGIN
if to_char(last_day(to_date('0102'||year, 'ddmmyyyy')), 'dd') = '29' then
v_days_in_year := 366;
end if;
FOR i in 0 .. v_days_in_year integer-1 LOOP
res.v_day := v_start_date + i;
pipe row(res);
END LOOP;

return;
END get_dates;

```

and you can use it:

```
select * from table(get_dates('2011'));

```