Database

Get Involved. Join the Conversation.

Topic

    Ivanv
    Setting NLS_LANGUAGE and NLS_TERRITORY at database level
    Topic posted April 12, 2017 by Ivanv Green Ribbon: 100+ Points, last edited April 26, 2017 
    11170 Views, 8 Comments
    Title:
    Setting NLS_LANGUAGE and NLS_TERRITORY at database level
    Summary:
    Setting NLS_LANGUAGE and NLS_TERRITORY at database level
    Content:

    Hi everyone,

    I need to set NLS_LANGUAGE and NLS_TERRITORY at database level (those from nls_database_parameters). We read that that we can set up those parameters only at database installation time (unlike NLS parameters at instance and session level), is it true?

    During Oracle Database Cloud installation wizard I didn't found any option to change language or NLS parameters of database and now for example NLS_LANGUAGE is AMERICAN not CROATIAN and NLS_TERRITORY is AMERICA not CROATIA.  This parameters at database level are important to us because Oracle optimizer uses them. How can we achieve this?

    Regards,

    Ivan

    Comment

     

    • Guilherme Santanna

      Hello Ivan,

          Actually what you can't change is the character set, the NLS parameters like NLS_LANGUAGE and NLS_TERRITORY are all set at session level on client side.

          So you can change them either using alter session set NLS_LANGUAGE=''; or if you want you can set at OS level, like export NLS_LANGUAGE="".

    • Guilherme Santanna

      Try below on client os side before starting your application.

      export NLS_LANG="CROATIAN_CROATIA.EE8MSWIN1250"

      Also changing the locale of the OS will make changes on it. Even if the database has a different one, the one that will be used is the client side configuration.

    • Chuck Lucas

      The optimizer uses SESSION based values: NLS_LANG can be set at the SESSION level and would over-ride database values.

      You can also use a  Logon Trigger

      Logon triggers can be set for specific users logging on based on your preference.

      • alter session set NLS_* ... =
      • alter system set NLS_* .... =
      • Logon trigger

      How To Set a NLS Session Parameter At Database Or Schema Level For All Connections? (Doc ID 251044.1)

      NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

      The Correct nls_lang Setting in Unix Environments(264157.1)

      nls_lang Client Settings and JDBC Drivers(115001.1)

      The one thing you will want to understand is that the ENV is very important vs. database settings.
      - There are a few cases where the database settings are important, but usually only a factor IF no ENV and Session settings have been used

      this includes the ENV starting the database, listener, sqlplus, agents...etc

    • Sailaja Pasupuleti

      Hi,

      In case of 12c Version with Multitenant Architecture on Cloud Database as a Service, hope the below also helps

      Step1:  Create Trigger at PDB Level

      CREATE TRIGGER...........AFTER STARTUP ON PLUGGABLE DATABASE......................

      Step2:  nls_language and nls_territory, set these at PDB Level as there are modifiable at PDB Level

      SQL> select name, ispdb_modifiable
        2  from v$system_parameter
        3  where ispdb_modifiable = 'TRUE';

      NAME                                     ISPDB
      ---------------------------------------- -----
      sessions                                 TRUE
      timed_statistics                         TRUE
      timed_os_statistics                      TRUE
      resource_limit                           TRUE
      nls_language                             TRUE
      nls_territory                            TRUE
      ..........................................................
      ..........................................................
      ..........................................................
      ..........................................................

      Regards,

      Sailaja

    • Ivanv

      Hi,

      Thank you for your help. We already know how to set NLS_LANG at the SESSION level, we altered the session and set NLS_LANGUAGE=CROATIAN, NLS_TERRITORY=CROATIA and NLS_SORT=CROATIAN. We get correct values from nls_session_parameters. We also exported and set OS LANG=hr_HR.UTF-8 and NLS_LANG=CROATIAN_CROATIA.AL32UTF8.

      NLS_LANGUAGE at DATABASE level is AMERICAN. When we execute SQL-s we get LANGUAGE_MISMATCH at sharing child cursor. How can we solve this problem? We read that we also need to set NLS params at DATABASE level to solve this issue.

      Regards,

      Ivan

    • Sailaja Pasupuleti

      Hi,

      The LANGUAGE_MISMATCH indicates that different NLS related settings, e.g. different NLS_SORT or NLS_COMP settings were used.  You can use a logon trigger to set NLS_COMP and NLS_SORT.

      Thank you,

      Regards,

      Sailaja

    • Ivanv

      Thank you!!

      You were right session parameters override those at database level!!

    • Ivanv

      Thank you!!

      You were right, LANGUAGE_MISMATCH was bacause of different NLS settings on client side. One of the SQL had correct NLS_SORT but was missing NLS_LANGUAGE and NLS_TERRITORY values.