Re: case-insensitive NLS_SORT

From: Adam Musch <ahmusch_at_gmail.com>
Date: Wed, 19 Aug 2009 11:16:07 -0500
Message-ID: <516d05a0908190916t76851f1cw9c9a643a3f12c6ef_at_mail.gmail.com>



If it's a windows client, it'd be an environment variable, which you'd access via Control Panel/System/Advanced/Environment Variables.

I really recommend you look at the Globalization Support Guide before messing around with the NLS parameters to make sure you're doing what you want to do at the level you need to do it. There's three separate sets of NLS parameters in play (database, instance, and session).

However, if you want it to work for all clients, regardless of the settings on the client, a logon triggers's the only method I've found which will work 100% of the time, because it gets invoked after all the defaults have been applied.

On Wed, Aug 19, 2009 at 10:22 AM, Xu, Roger<Roger.Xu_at_dpsg.com> wrote:
> Do you where and how do we change the client installation's NLS values? Windows 2003 Server is the client. Thanks!
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Adam Musch
> Sent: Wednesday, August 19, 2009 9:40 AM
> To: wellmetus_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: case-insensitive NLS_SORT
>
> A login trigger using ALTER SESSION calls is about the only workable
> solution. †The client installation's NLS values override any defaults
> set at the instance or database level.
>
> On Wed, Aug 19, 2009 at 9:35 AM, Roger Xu<wellmetus_at_gmail.com> wrote:
>> Hi Experts,
>>
>> We are trying to make our 10g database (Linux) case-insensitive by setting
>> the following two parameters.
>>
>> SQL> show parameter NLS_COMP
>> NAME † † † † † † † † † † † † † † † † TYPE † † † †VALUE
>> ------------------------------------ ----------- ----------
>> nls_comp
>> † † † † † † † † † † † † † † †string † † †ANSI
>> SQL> †show parameter NLS_SORT
>> NAME † † † † † † † † † † † † † † † † TYPE † † † †VALUE
>> ------------------------------------ ----------- ------------
>> nls_sort † † † † † † † † † † † † † † string † † †BINARY_CI
>>
>> However, it does not work if we connect to the DB from a client(Windows).
>>
>> SQL> select sys_context('USERENV','NLS_SORT') from dual;
>> SYS_CONTEXT('USERENV','NLS_SORT')
>> --------------------------------------------------------------------------------
>> BINARY
>> SQL>
>> What to do so it works for all clients without doing "Alter Session ...."?
>>
>> Thanks,
>>
>> Roger Xu
>>
>> P.S. Someone asked the same question below but I had a hard time to find out
>> the solution.
>>
>> http://www.orafaq.com/forum/t/74537/0/
>>
>
>
>
> --
> Adam Musch
> ahmusch_at_gmail.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> http://www.drpeppersnapplegroup.com/about/corporate to learn more about Dr Pepper Snapple Groupís commitment to corporate social responsibility.
>
> Please be conscious of the environment and print this email only if absolutely necessary.
> This e-mail (including any attachments) is confidential and may contain privileged information of Dr Pepper Snapple Group, Inc. and/or its subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended recipient or receive it in error, you may not use, distribute, disclose or copy any of the information contained within it and it may be unlawful to do so. If you are not the intended recipient, please notify us immediately by returning this e-mail to us at mailto:mailerror_at_dpsg.com and destroy all copies. Any views expressed by individuals within this e-mail do not necessarily reflect the views of Dr Pepper Snapple Group. This e-mail does not constitute a binding offer, acceptance, amendment, waiver or other agreement, unless the intent that an e-mail will constitute such is clearly stated in the body of the email. Recipients are advised to subject this e-mail and attachments to their own virus checking, in keeping with good computing practice. Please note that e-mail
  received by Dr Pepper Snapple Group may be monitored in accordance with applicable law.
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 19 2009 - 11:16:07 CDT

Original text of this message