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

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

Leave a Reply

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