6 Replies Latest reply: Apr 21, 2011 10:14 AM by Sergiusz Wolicki-Oracle RSS

    Setting NLS_COMP and NLS_SORT for all Sessions

    849256
      Hi all,

      I searched in the Forum/Google/etc,... but unfortunaly I don't found a solution.

      For our Application we need an case insenstive search. So if we set the change the Session Parameters to NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI it works fine. Now set this two Parmeters in the NLS_INSTANCE_PARAMETERS.
      Now when I start SQLPLUS and check the NLS_SESSION_PAREMTERS they NLS_COMP is correct but the NLS_SORT is set to NLS_SORT=GERMAN .

      Is there are way to force this Paremters from INSTANCE so that every Client always use the Parameter from NLS_INSTANCE_PARAMETERS?

      Due that it's not an own Application we can't use ALTER SESSION SET .... .

      regards
      Thomas
        • 1. Re: Setting NLS_COMP and NLS_SORT for all Sessions
          Zoltan Kecskemethy
          You can setup this in a logon trigger. You can check the user name and setup the session parameters for the application user only.
          there is an example here e.g. http://www.dba-oracle.com/oracle_tips_ault_custom_parameters_users.htm
          or even a better here: http://psoug.org/reference/system_trigger.html

          BTW if you application uses a connection pool it has a feature to setup session parameters or auto execute an sql script to setup session parameters next to the login information...
          • 2. Re: Setting NLS_COMP and NLS_SORT for all Sessions
            19426
            You could define the parameters as environment variables. If not explicitly set, NLS_SORT is derived from NLS_LANGUAGE and NLS_LANGUAGE (if also not explicitly set) is derived from NLS_LANG.
            So your settings in pfile/spile get always overwritten, when you start a sqlplus session.

            Werner
            • 3. Re: Setting NLS_COMP and NLS_SORT for all Sessions
              Sergiusz Wolicki-Oracle
              It depends on how the application connects to the database. If it uses OCI directly or through ODBC (and, I suppose, ODP .NET), setting NLS_SORT and NLS_COMP as environment variables is the easiest method. If the application connects through JDBC Thin, you have to use the logon trigger. Neither environment variables nor the logon trigger will work for NLS_SORT with JDBC OCI (though both should work for NLS_COMP with JDBC OCI).


              -- Sergiusz
              • 4. Re: Setting NLS_COMP and NLS_SORT for all Sessions
                849256
                Hi all,

                The solution with the trigger solved the problem.

                Thanks all for the Help.

                Regards
                Thomas
                • 5. Re: Setting NLS_COMP and NLS_SORT for all Sessions => Performance Problem
                  849256
                  Hi all,

                  Te solution works fine but after implementing i run into a performance Problem. I can't say detailed information about the Performance issue because I don't have the knowledge. I only can say that after more ore less one day the Application is very slow when they access the database.

                  Does anybody has an idea how I can improve the Performance from this Trigger?

                  Regards
                  Thomas
                  • 6. Re: Setting NLS_COMP and NLS_SORT for all Sessions => Performance Problem
                    Sergiusz Wolicki-Oracle
                    Setting NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI affects all relational operators in all SQL executed by affected sessions. This is why it is usually not a good idea. In most cases, the performance issues are caused by the fact that normal indexes cannot be used in processing of linguistic comparisons. You need to redefine them as functional indexes on NLSSORT(column,'NLS_SORT=BINARY_CI').

                    In general, applications should be modified to be more "fine-grained" in their use of linguistic operations. There is no better solution yet.


                    -- Sergiusz