Oracle11g case sensitivity problem

[divyakprabh]

Hi,

Im using Oracle 11g, and i have a query like

select * from test where ID_PRIVILEGES = 'ADMIN';

but in database ID_PRIVILEGES is stored as Admin, I want case insensitive search to be done. I cannot change my query as

select * from test where ID_PRIVILEGES = 'Admin';

Can anyone please help.

[urtrivedi]

I used to convert things in upper case while comparing strings in oracle

select * from test where upper(ID_PRIVILEGES) = upper('Admin');

You may also follow this link
http://geekzspot.blogspot.com/2010/01/case-insensitive-oracle-database.html

[divyakprabh]

Thanks a lot for ur reply,

if i execute this command, ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE;

i get,[SQL] ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE
[Err] ORA-00927: missing equal sign

should i give the path of SPFILE in this statement.?

[urtrivedi]

I am not sure about the command, but you may have noticed this line in that link

Your client tool may be overriding your carefully set NLS Parameters. For example, in SQL Developer and JDeveloper (Tools -> Preferences… -> Database -> NLS Parameters)

Check the database parameters:

select * from nls_session_parameters;

This could also help

ALTER session SET NLS_COMP=LINGUISTIC 
ALTER session SET NLS_SORT=BINARY_AI

[divyakprabh]

Thanks a lot, it worked fine when i directly executed the query in navicat, but from my java application it still returning null.

[urtrivedi]

I think when you connect in java, you must always run this two query before doing anything, because change is applicable to session only. TO CHANGE IT PERMANANTELY YOU MUST HAVE SYSTEM USER ACCSESS

[divyakprabh]

i have included these two statements in init.ora,

ALTER SYSTEM SET NLS_COMP = "LINGUISTIC" SCOPE SPFILE;
ALTER SYSTEM SET NLS_SORT = "BINARY_CI" SCOPE SPFILE;