How to edit NLS parameters in Toad for Oracle and Oracle SQL Developer?

Category: Tricks n TechniquesTags: , , ,

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.

sql developer nls settings

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.

Toad NLS settings

Share

0 comments

Your email address will not be published. Required fields are marked *