The default is case sensitive, so the following query returns no lowercase. In this case, the returned result will contain missing data.
select * from person where name like 'A%' ;
If the result of the query must return both upper and lower case records, the following changes must be made.
NLS_COMP = LINGUISTIC and NLS_SORT = BINARY_AI
If it is desired to be valid for all users, it can be changed as a parameter.
If you only want to apply to specific users, you can do this with the database logon trigger.
Change parameter for all users:
SQL> alter system set NLS_COMP=LINGUISTIC scope=spfile sid='*';
SQL> alter system set NLS_SORT=BINARY_AI scope=spfile sid='*';
The database must then be restarted.
Database Logon Trigger for spesific user:
If you want it to be valid only for a user , you can do this with logon trigger as follows at the session level , which can only be valid during the session when the user logs in.
CREATE OR REPLACE TRIGGER SYS.CASE_INSENSITIVE_TRIGGER
AFTER LOGON ON DATABASE
if (sys_context('USERENV', 'SESSION_USER') in ('USER1','USER2')) then
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_AI';