Link to home
Create AccountLog in
Avatar of sns-emea
sns-emeaFlag for United Arab Emirates

asked on

Disable Oracle 11gr2 Case Sensitivity

Oracle 11 select are case sensitive. I have check online and noticed that if i use the alter session scripts, i am able to retrieve records regardless of case sensitivity:

 alter session set NLS_COMP=ANSI;

 alter session set NLS_SORT=BINARY_CI;

select * from x where user='XXX';

Result:

Xxx
xxx
XXX
xXX

If i add those to the database parameters in the spfile and pfile, bring back the db online, check parameter as below:

show parameter NLS_COMP

ANSI

show parameter NLS_SORT

BINARY_CI
 
if i select from the table:

select * from x where user='XXX';

Result:

XXX

What have i missed? How can alter the database to case insensitive?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>How can alter the database to case insensitive?

You cannot.  You can create triggers to force case on insert/update or force case on select:

select * from x where upper(user)='XXX';

Also regexp can be case insensitive:
select * from x where regexp_instr(user,'XXX',1,1,1,'i') > 0;

You can also create an Oracle text index.  These are not case sensitive.

ASKER CERTIFIED SOLUTION
Avatar of jaiminpsoni
jaiminpsoni
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@sns-emea,
Did you try with the logon trigger? Did it work in your environment?

Thanks,
Jaimin Soni
Avatar of sns-emea

ASKER

I will try and let you know
Hi buddy,
Any updates?

Can we close this question now?

Thanks,
Jaimin Soni
Didnt try yet.
Creating the above will cause issues on the sorting in oracle but answered my main questions.