I'm working on developing an application that will be multi-lingual. I need to replace a bit of text in a string with a date in dd Month yyyy format. In order for the month to display in the user's selected language, I need to do a to_char using the NLS_LANGUAGE option but I can't seem to get it to work. No matter what language I use, I get the result in English.
SQL> select to_char(SYSDATE,'dd Month yyyy','NLS_LANGUAGE=GERMAN') FROM dual;
TO_CHAR(SYSDATE,'DDMONTHYYYY',
--------------------------------------------
11 March 2013
SQL> select to_char(SYSDATE,'dd Month yyyy','NLS_LANGUAGE=FRENCH') FROM dual;
TO_CHAR(SYSDATE,'DDMONTHYYYY',
--------------------------------------------
11 March 2013
SQL> select to_char(SYSDATE,'dd Month yyyy','NLS_LANGUAGE=ITALIAN') FROM dual;
TO_CHAR(SYSDATE,'DDMONTHYYYY',
--------------------------------------------
11 March 2013
I must have the syntax correct because if I use an invalid language I get an error.
SQL> select to_char(SYSDATE,'dd Month yyyy','NLS_LANGUAGE=XXX') FROM dual;
select to_char(SYSDATE,'dd Month yyyy','NLS_LANGUAGE=XXX') FROM dual
ORA-12702: invalid NLS parameter string used in SQL function
How do I get this to work correctly?