0

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.

2
Contributors
6
Replies
7
Views
6 Years
Discussion Span
Last Post by divyakprabh
0

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.?

0

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

Edited by urtrivedi: ADDED 2 LINES

0

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

0

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

0

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;

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.