When I found that changing the NLS parameters in the Oracle will make the search query case insensitive, I tried to find a way to change the NLS parameters in Toad for Oracle and Oracle SQL Developer in order to change it every time I login to Oracle. Changing the NLS parameter in Oracle SQL Developer was quite easy but it took a while to do the same in Toad for Oracle. Here is a technique to make the change in NLS parameters in both the applications.
Oracle SQL Developer
Go to Oracle SQL Developer > Tools > Preferences.. > Database > NLS.
Toad for Oracle
- You may go to Toad for Oracle > Database > Administer > NLS Parameters screen. If you have a DB Admin module, then and only then you will be able to modify the NLS parameter.
- If you don’t have DB Admin module, you can setup a startup sql script which will be executed when the new connection is created. Use the following SQL script and modify as per your need.
/*Following statements change the case sensitive search result to case insensitive search result*/ ALTER SESSION SET NLS_COMP=LINGUISTIC; ALTER SESSION SET NLS_SORT=BINARY_CI;
Name this file as “Toad_Startup.sql” and add it as a startup script in Toad.