NLS setting for multilanguages

Question

My current application is running with NLS_lang as American.america on Oracle forms. We are planning make this application available on multilanguage. we are in the process of the modifying the code to handle application labels, prompts,hint, messages,etc run time. we dont want to change the time/date formats and leave it as it would in American.america. But for example, When i change the NLS_lang to French_canadian or anything else. Along with the language the date formats and date language paramters are changing. I am in need to change the NLS_lang as that is the only way i can translate the oracle error messages. But my date formats having DD-MON-YYYY format in Oracle DB create a problem in my weblogic sesssion. Is there are way to only change my weblogic session for my forms to be French_canadian but leave the date formats/ date format languages in American? Can we make this happen? except Language other paramters to remain same as american??? Please help.

Advance thanks for your time and valuable ideas...

Answer 1

You can set language and date/time formats individually. Have a look at these parameters:

• NLS_DATE_FORMAT
• NLS_DATE_LANGUAGE
• NLS_LANGUAGE
• NLS_TERRITORY
• NLS_NUMERIC_CHARACTERS

NLS_LANG is used to define the default for all these, but each of them can be different. With NLS_TERRITORY you can commonly set "NLS_DATE_FORMAT", "NLS_NUMERIC_CHARACTERS", "NLS_CURRENCY", and "NLS_ISO_CURRENCY"

See example here:

ALTER SESSION SET NLS_TERRITORY = 'germany';
ALTER SESSION SET NLS_LANGUAGE = 'polish';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'french';

SELECT SYSDATE AS german_date_format, TO_CHAR(SYSDATE, 'Day') AS french_day FROM dual;
SELECT 1/0 AS polish_error_message FROM dual;

GERMAN_DATE_FORMAT   FRENCH_DAY
--------------------------------
23.01.14             Jeudi


SELECT 1/0 AS polish_error_message FROM dual
                 *
Error at line 1
ORA-01476: dzielnik jest równy zero