ORACLE 11g case insensitive by defalt

Question

Hi all, I found in this article, that since ORACLE 10g, there is a way to make a particular connection-session compare strings case-insensitive, without needing any crazy SQL functions, using an ALTER SESSION.

Does anyone know if, in 11g, there might be a way to make the database to always operate in this mode by default for all new connection-sessions, thereby eliminating the need for running ALTER SESSIONs every time you connect?

Or perhaps, an additional parameter you could specify on your connection string that would turn the same on?

Answer 1

You could just set the NLS_SORT, NLS_COMP parameters mentioned in the article as the values in the the Oracle init file using the alter system set <parameter> = <value>; clause.

Info on using the alter system commands can be found here.

Here is a good link on the correct usage of the NLS_* parameters. Note that some settings of of the NLS_SORT parameter can/could cause performance issues, namely when it is not set to BINARY. The Oracle docs state:

Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.

Answer 2

Sure you can!

Get ur friendly DBA to set these paramaters:

ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE;

ALTER SYSTEM SET NLS_SORT=BINARY_AI SCOPE SPFILE;

This is taken from my short article on How to make Oracle Case Insensitive