Use SQL to Create a Calendar in Mutliple Languages

In his post titled learning foreign languages with Oracle SQL, Matthias Rogel shares the following query (I slightly modified and formatted it for clarity):

  WITH calendar
       AS (    SELECT ADD_MONTHS (DATE '2012-01-01', LEVEL - 1) mon_date
                ,TO_CHAR (ADD_MONTHS (DATE '2012-01-01', LEVEL - 1), 'Month')
                         mon_name
                 FROM DUAL
           CONNECT BY LEVEL <= 12)
    SELECT nls.VALUE AS language
          ,calendar.mon_name AS mon_name
          ,TO_CHAR (
              calendar.mon_date
             ,'Month'
             ,q'[nls_date_language=']' || nls.VALUE || q'[']'
           )
              AS translated_mon_name
          ,TO_CHAR (
              calendar.mon_date
             ,'MON'
             ,q'[nls_date_language=']' || nls.VALUE || q'[']'
           )
              AS translated_mon_name_short
      FROM v$nls_valid_values nls, calendar
     WHERE nls.parameter = 'LANGUAGE'
  ORDER BY nls.VALUE, calendar.mon_date;

The output is a list of the 12 calendar months translated to different languages. If you do not have an Oracle database handy to run the query now, you can browse the result here.

Matthias uses several interesting techniques and features in his query:

Subquery Factoring: The WITH clause is used to name and separate the subquery that returns the 12 months. More about Subquery Factoring:

• Documentation
• AskTom thread
• oracle-developer.net
• Oracle Blogs

Row Generator: The CONNECT BY LEVEL method is used to generate 12 rows corresponding to the 12 months. More about generating rows:

• OraQA.com
• SQLsnippets.com

Date Literal: DATE ’2012-01-01′is used to convert the string 2012-01-01 to a date datatype. More about date literals:

• Documentation
• Steven Feuerstein

v$nls_valid_values: This view is used to select all available languages. More about v$nls_valid_values:

• Documentation
• MorgansLibrary.org

Cartesian Join: A Cartesian Join, a.k.a. cross join, is used to join between v$nls_valid_values and the calendar subquery. This assures returning 12 records (months) for each language. More about Cartesian Joins:

• Documentation
• Eddie Awad
• Charles Hooper
• Sheeri Cabral

nls_date_language: The nls_date_language option in the to_char function is used to explicitly specify the language to use for the spelling of month names and abbreviations. More about nls_date_language:

• Documentation
• Laurent Schneider

Q-quote mechanism: q’[ is used to quote the the language in the to_char function. Q-quoting enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter. More about the Q-quote mechanism:

• Eddie Awad
• OraQA.com
• Martin Giffy D’Souza

This is just one relatively simple example of the power of SQL. For more examples of what you can do with SQL check out OraQA.com.

Filed in Oracle on 02 Apr 12

---