In Oracle Database, the NLS_LANGUAGE initialization parameter specifies the default language of the database.

This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism.

The NLS_LANGUAGE parameter also determines the default values of the NLS_DATE_LANGUAGE and NLS_SORT parameters.

This article outlines how to change the value of the NLS_LANGUAGE and NLS_DATE_LANGUAGE parameters.

Check the Initial Settings

First, let’s check my current settings for these parameters:

SELECT
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_LANGUAGE',
    'NLS_DATE_LANGUAGE',
    'NLS_SORT'
    );

Result:

           PARAMETER       VALUE 
____________________ ___________ 
NLS_LANGUAGE         AMERICAN    
NLS_DATE_LANGUAGE    AMERICAN    
NLS_SORT             BINARY      

Change the NLS_LANGUAGE Parameter

We can use the ALTER SESSION statement to change the setting for the the NLS_LANGUAGE parameter.

Example:

ALTER SESSION SET NLS_LANGUAGE = 'BASQUE';

Result:

Session altered.

Let’s check the new values:

SELECT
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_LANGUAGE',
    'NLS_DATE_LANGUAGE',
    'NLS_SORT'
    );

Result:

           PARAMETER        VALUE 
____________________ ____________ 
NLS_LANGUAGE         BASQUE       
NLS_DATE_LANGUAGE    BASQUE       
NLS_SORT             GENERIC_M    

We can see that both the NLS_LANGUAGE and NLS_DATE_LANGUAGE parameters have been changed to the specified language, even though we only updated the NLS_LANGUAGE parameter.

We can also see that the NLS_SORT parameter has been updated to the default sort for the BASQUE language.

Change the NLS_DATE_LANGUAGE Parameter

Now let’s change the NLS_DATE_LANGUAGE parameter:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'SPANISH';

Result:

Session altered.

And now check the new values:

SELECT
    PARAMETER,
    VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER IN (
    'NLS_LANGUAGE',
    'NLS_DATE_LANGUAGE',
    'NLS_SORT'
    );

Result:

           PARAMETER        VALUE 
____________________ ____________ 
NLS_LANGUAGE         BASQUE       
NLS_DATE_LANGUAGE    SPANISH      
NLS_SORT             GENERIC_M    

So we have explicitly changed the value of the NLS_DATE_LANGUAGE parameter without changing the other two.

The default sort for SPANISH is SPANISH, and we can see that the NLS_SORT value is still GENERIC_M, so it hasn’t been affected.