How to get the month list?

How to get the month list? even with another language!
A great use of CONNECT BY :-)
Its works only with Oracle 9i and upper (due to CONNECT BY).




DECLARE
l_language nls_session_parameters.VALUE%TYPE;
BEGIN
-- stores the previous language session
SELECT VALUE
INTO l_language
FROM nls_session_parameters
WHERE parameter = 'NLS_LANGUAGE';

-- changes to another language (PORTUGUESE in the example)
DBMS_SESSION.set_nls ('NLS_LANGUAGE', 'PORTUGUESE');

--main loop ....the SQL gives the month and the number of sequence
FOR c IN
(SELECT TO_CHAR (ADD_MONTHS (TO_DATE ('01/01/2000', 'DD/MM/RRRR')
, r.l - 1
)
, 'MONTH'
) AS month_descr
, r.l AS month_ind
FROM (SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <= 12) r ORDER BY 2)
LOOP
DBMS_OUTPUT.put_line (c.month_ind || ' - ' || c.month_descr);
END LOOP;
-- change the language to the previous one :-)
DBMS_SESSION.set_nls ('NLS_LANGUAGE', l_language);
END;


The same could be used to give the name of the days.

Portuguese Version / Versão em Português