Link to home
Start Free TrialLog in
Avatar of axnst2
axnst2Flag for United States of America

asked on

How do I change the default case sensitivity under Oracle 10g/11g?

Hi Experts,

      I need to be able to change the default case sensitivity for an Oracle database/schema.  So basically I if I run a SQL query like this:

" SELECT * FROM USERS WHERE UserName='Tom'; "

I would like it to return 'Tom', 'TOM', and 'tom' as well!  I saw that you can do some thing like this:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16370675423662

but I am wondering if I can just somehow set this as the default for the entire schema/database?

Thanks!
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

Connect as sys OR SYSTEM and change the parameter to false.

    alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;
Avatar of David VanZandt
I've read that PostgreSQL was working on this a while back, and i have not stumbled across this in any Oracle new features discussion.  A variation on the special index is to use regular expressions for all of your DML, not recommended.  So, I think "no", but I'll look further.

BTW, the SEC_CASE_SENSITIVE_LOGON parameter is for password sensitivity only, in 11g -- not for data manipulation.
Avatar of axnst2

ASKER

Connect as sys OR SYSTEM and change the parameter to false.

    alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;

Wouldn't that only change the case sensitivity for the login passwords?
You can do it at the system level, but it's not a good idea. Even the link you provided, Tom is telling you the following:

it'll make everything case insensitive will be the impact.  Not sure I'd do it system wide, but via alter sessions in the app or via an on logon trigger -- sure.

ALTER SYSTEM set nls_sort=BINARY_CI scope BOTH
Avatar of axnst2

ASKER

OK, I like the idea of a logon logon trigger.  What would that CREATE script look like?
ASKER CERTIFIED SOLUTION
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of axnst2

ASKER

Thanks!