I noticed this question on the OTN forum about how to populate a select list with the current and past months of the year. The person who asked this question was using a hierarchical query in combination with SYSDATE and some date functions to reach a solution.
Too complex and cluttered in my opinion, so I started Googling for inspiration. That brought me to the following Stack Overflow topic: List all the months using Oracle SQL. One of the answers on that topic simply used a (relatively unknown) APEX view to return all months of the year. WWV_FLOW_MONTHS_MONTH is a public view owned by the APEX schema. That means of course that the view is only available when Oracle Application Express is installed in the database.
> desc wwv_flow_months_month; Name Null Type ------------- ---- ------------- MONTH_DISPLAY VARCHAR2(128) MONTH_VALUE NUMBER > select * from wwv_flow_months_month; MONTH_DISPLAY MONTH_VALUE ------------- ----------- January 1 February 2 March 3 April 4 May 5 June 6 July 7 August 8 September 9 October 10 November 11 December 12
In case you prefer short names for the months (e.g. Jan, Feb, Mar), you can query the WWV_FLOW_MONTHS_MON view instead.
Having these views at your disposal, makes it really easy to return a list of the current and past months of the year.
select month_display as d, month_value as r from wwv_flow_months_month where month_value <= to_number(to_char(sysdate, 'mm'));
The APEX Application Builder User’s Guide includes a section that lists all publicly available objects exposed by the APEX product schema. You might find other interesting and helpful views on that page. The WWV_FLOW_YEARS view for example holds an extensive list of years from 1920 to 2050. This might come in handy when having to create a select list with years to choose from.