Friday, April 2, 2010

Case Sensitiveness

Oracle maintains the case sensitiveness by default .


To make the queries , we would need to change the NLS settings to enforce case insenstiveness .

Here is an example ....

Note : As usual , please regression test your application before making these changes


SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string


SQL>



SQL> select * from scott.emp where ename like 'smith';
no rows selected


SQL> alter session set nls_comp=linguistic;
Session altered.

SQL> alter session set nls_sort = binary_ci;
Session altered.

SQL> select * from scott.emp where ename like 'smith';


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20


SQL> select * from scott.emp where ename like 'sMith';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20

SQL> select * from scott.emp where ename like 'SMITH';


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20

No comments:

Post a Comment