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

By | May 1, 2015

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

Leave a Reply

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