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.
Interesting and very useful one.
LikeLiked by 2 people
Rather than relying on a view that may or may not be there is future versions why not just use CONNECT BY with dual?
SELECT ADD_MONTHS(TRUNC(SYSDATE,’MM’),level*-1)
FROM dual
CONNECT BY level <= :number_months
LikeLiked by 2 people
Sure, that’s a possibility too. However, I find that query too “complex” for what it does. When you read the query on WWV_FLOW_MONTHS_MONTH, you know immediately what it does.
The hierarchical query requires more brainwork to actually understand what the result will be. It’s a creative solution, but why would you need a hierarchical query on DUAL in combination with a complex calculation to simply list the months of the year?
If you don’t trust the WWV_FLOW_MONTHS_MONTH view, you can always create your own months table.
LikeLiked by 2 people