Avatar of axnst2
axnst2
Flag 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!
Oracle Database

Avatar of undefined
Last Comment
axnst2

8/22/2022 - Mon
Muhammad Ahmad Imran

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

    alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;
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.
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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
paquicuba

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
axnst2

ASKER
OK, I like the idea of a logon logon trigger.  What would that CREATE script look like?
ASKER CERTIFIED SOLUTION
David VanZandt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
axnst2

ASKER
Thanks!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.