Re: Case insensitive search

Mladen,

Problem is not that i can't change it at the instance level but it was not taking affect after restarting the instance, anyhow I found the following from oracle docs that means that initialization level have a lower priority and even though we are not setting these parameters as enviroment variables they we get derived from NLS_LANG setting in the registry entry:
Table 3-1 Methods of Setting NLS Parameters and Their Priorities Priority Method
1 (highest)
Explicitly set in SQL functions
2
Set by an ALTER SESSION statement
3
Set as an environment variable
4
Specified in the initialization parameter file 5
Default

Thanks
--Harvinder

On Jan 31, 12:21 am, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Tue, 30 Jan 2007 14:20:50 -0800, harvinder76 wrote:
> > Hi,
>
> > When we set the following parameters on the client machine with alter
> > session it works fine and we get case insensitive searches but when we
> > try to set this in spfile it only affects the sessions on the database
> > machine:
> > NLS_SORT='BINARY_CI'
> > nls_comp='LINGUISTIC'
>
> > Is there any way we can change this settings at the instance level so
> > that we don't have to use logon trigger to set this parameters at
> > session level?
>
> Harvinder, now you are being childish. These two are parameters which
> can easily be modified in the spfile.
>
> SQL> alter system set NLS_SORT='BINARY_CI' scope=memory;
> alter system set NLS_SORT='BINARY_CI' scope=memory
> *
> ERROR at line 1:
> ORA-02096: specified initialization parameter is not modifiable with this
> option
>
> SQL> alter system set nls_comp='LINGUISTIC'scope=memory;
> alter system set nls_comp='LINGUISTIC'scope=memory
> *
> ERROR at line 1:
> ORA-02096: specified initialization parameter is not modifiable with this
> option
>
> It would be trivial to write them with "scope=spfile" option and restart
> the instance.
> I've seen you many times on this group, you're not a beginner. You must
> have known that the values you're talking about are instance
> parameters which are modified solely by thy DBA. The fact that you are
> asking the question means that you cannot do that yourself and that you're
> asking us how to circumvent your DBA's commandments. Naughty, naughty! Do
> not mention thy DBA's name in vain and above all, do not try to go around
> his policies, lest you want to experience his wrath.
>
> ---http://www.mladen-gogala.com
Received on Wed Jan 31 2007 - 09:53:19 CST