ALTER SESSION SET NLS_LANGUAGE Oracle Tips by Burleson Consulting

 

Question:

Through combining v$session & v$sqlarea, I notice some (if not all?) of my applications seem to have sessions running the following SQL:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY'

Any idea why these applications are performing these ALTER SESSION commands?

Answer:

The Oracle documentation notes this about "alter session set nls_language":

The purpose of the ALTER SESSION statement is "to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database."

These "alter session" commands take the default values from your init.ora and ensure that the session is using the proper SQL environmental variables.

For example, a query that returns a DATE would have a very different display depending on your setting for nls_date_format!

The nls_lang parameter is an important component for any Oracle database that displays non-English characters.  You can set nls_lang in your initialization parameter file (init.ora) and you can also set it at the session level and within shell scripts:

NLS_LANG='english_united kingdom.we8iso8859p1'; export NLS_LANG 

The best place to look for nls_lang parameter information is the Oracle Globalization Support Guide

Also, see my related notes on "alter session set nls_language":