The default is case sensitive, so the following query returns no lowercase. In this case, the returned result will contain missing data.
|
1 |
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:
|
1 2 |
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.
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE TRIGGER SYS.CASE_INSENSITIVE_TRIGGER AFTER LOGON ON DATABASE BEGIN 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'; end if; END case_insensitive_trigger; / |
