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

axnst2
axnst2 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Muhammad Ahmad ImranDatabase Developer

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

    alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;
David VanZandtOracle Database Administrator III

Commented:
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.
axnst2IT Manager

Author

Commented:
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?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
axnst2IT Manager

Author

Commented:
OK, I like the idea of a logon logon trigger.  What would that CREATE script look like?
Oracle Database Administrator III
Commented:
One improvement, use ALTER SESSION instead of (the privilege-requiring) ALTER SYSTEM.

From Oracle Magazine Tip for Week of June 26, 2006:

CREATE OR REPLACE TRIGGER ON_LOGON_SCOTT
AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )
BEGIN
execute immediate 'ALTER SESSION set nls_sort=BINARY_CI scope BOTH';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
axnst2IT Manager

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial