This discussion is archived
8 Replies Latest reply: Feb 22, 2010 11:56 AM by sb92075 Branched to a new discussion. RSS

Can case insensitive be set at instance level?

626620 Newbie
Currently Being Moderated
In Oracle 11.2, to set case insensitive search in a session, one can readily do below
alter session set NLS_COMP=LINGUISTIC; 
alter session set NLS_SORT=BINARY_CI;
select * from glu.test_ci where name = 'Johny Jacobson';

NAME
-------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON
Now I want to set the instance like that. I did
alter system set NLS_COMP=LINGUISTIC scope=spfile;
alter system set NLS_SORT=BINARY_CI scope=spfile;

I restart the database and check
select name,value,display_value,isdefault from v$vw_parameter where name in ('nls_sort','nls_comp');

NAME                             VALUE                            DISPLAY_VALUE                    ISDEFAULT
-------------------------------- -------------------------------- -------------------------------- -
nls_sort                         BINARY_CI                        BINARY_CI                        FALSE
nls_comp                         LINGUISTIC                       LINGUISTIC                       FALSE

Made sure the parameters were changed and I ran the query
select * from glu.test_ci where name = 'Johny Jacobson';
NAME
----------------------------
Johny Jacobson
Only one row returned as case sensitive. To make cases insensitive happen I have to change the two parameter in the current session
alter session set NLS_COMP=LINGUISTIC; 
alter session set NLS_SORT=BINARY_CI;
select * from glu.test_ci where name = 'Johny Jacobson';

NAME
-------------------
Johny Jacobson
johny jacobson
JOHNY JACOBSON
How to het the whole database case insensitive?
Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points