Got solution for this on Oracle forum.

var start_date varchar2(10)

exec :start_date := '2010-01-01'

WITH years AS (

SELECT to_date(:start_date,'YYYY-MM-DD') -1 +level dt

FROM dual

CONNECT BY LEVEL <= (select round( SYSDATE-to_date(:start_date,'YYYY-MM-DD')) from dual)

)

Select

to_char(dt ,'yyyy') * 10000 + to_char(dt,'mm') * 100 + to_char(dt,'dd') As Date_ID

,dt As "Date"

,to_char(dt ,'yyyy') As Year

,to_char(dt,'MM') As Month_Number

,to_char(dt,'Month') As Month

,to_char(dt,'dd') As DayOfMonth

,to_char(dt,'Day') As DayOfWeek

,to_char(dt,'D') As DayOfWeekNo

,to_char(dt,'Q') As Quarter

From years;

Thanks for your posts. 🙂