Where can I query an oracle databases' case-sensitivity? Where can I query an oracle databases' case-sensitivity? oracle oracle

Where can I query an oracle databases' case-sensitivity?


In Oracle 10gR2:

SELECT  *FROM    NLS_SESSION_PARAMETERSWHERE   parameter IN ('NLS_COMP', 'NLS_SORT')SQL> ALTER SESSION SET NLS_COMP = 'LINGUISTIC'  2  /Session alteredSQL> SELECT  COUNT(*)  2  FROM    dual  3  WHERE   'a' = 'A'  4  /  COUNT(*)----------         1SQL> ALTER SESSION SET NLS_COMP = 'BINARY'  2  /Session alteredSQL> SELECT  COUNT(*)  2  FROM    dual  3  WHERE   'a' = 'A'  4  /  COUNT(*)----------         0

From documentation:

NLS_COMP specifies the collation behavior of the database session.

Values:

  • BINARY

    Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

  • LINGUISTIC

    Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

  • ANSI

    A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC


In addition to the answers already given be aware that case sensitivity changes in 11g - e.g. see the 11g documentation re passwords.


For Oracle 10gR2 (and later), the parameters are NLS_COMP and NLS_SORT.

select * from v$nls_parameters where parameter in ('NLS_COMP','NLS_SORT');

(These parameters are set at the session level. The settings for a session are inherited from the database setting, unless overridden by setting an OS environment variable, or an ALTER SESSION statement.)

If you want "case-insensitive" sorting and string matching, you can try these settings:

alter session set NLS_SORT=BINARY_CI;alter session set NLS_COMP=LINGUISTIC;

Those aren't the only settings for the parameters, of course. Oracle 10gR2 documentation:

10gR2 Linguistic Sorting and String Searching