Making Oracle 9i & 10g Case Insensitive

Sometimes it's useful to perform queries in Oracle that fetches all similar rows without worrying of uppercase or lowercase.

For example:

SELECT name FROM scott.users WHERE name LIKE '%Tiger%'

With case insensitive all results that contains the word "Tiger" are fetched, even the followings:

• TIGER
• tiger
• TigeR

And all the possible variations.

These are the scripts to make a single session in Oracle 9i case insensitive:

ALTER SESSION SET NLS_COMP = ANSI;
ALTER SESSION SET NLS_SORT = GENERIC_BASELETTER;

These are the scripts to make a single session in Oracle 10g case insensitive:

ALTER SESSION SET NLS_COMP = LINGUISTIC;
ALTER SESSION SET NLS_SORT = BINARY_CI;

To make the session case sensitive again (Oracle 9i & Oracle 10g):

ALTER SESSION SET NLS_COMP = BINARY;
ALTER SESSION SET NLS_SORT = BINARY; 

This query will help checking the parameters of the database, instance and session:

SELECT d.parameter Dparameter, d.value Dvalue, i.parameter Iparameter, i.value Ivalue,
   s.parameter Sparameter, s.value Svalue
FROM nls_database_parameters d, nls_instance_parameters i, nls_session_parameters s
WHERE d.parameter = i.parameter (+) AND d.parameter = s.parameter (+)
ORDER BY 1;

Thanks to Don Burleson for publishing this SELECT statement Read more about NLS parameters here