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 inPL/SQL
blocks is binary unless you specify theNLSSORT
function.
LINGUISTIC
Comparisons for all
SQL
operations in theWHERE
clause and inPL/SQL
blocks should use the linguistic sort specified in theNLS_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 setNLS_COMP
toLINGUISTIC
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: