In this article, we will discover what is NLS in Oracle. We will learn what this acronym stands for and what the NLS data format is. Also, we will get to know how it can be set in Oracle for the user session.
NLS in Oracle: what is it?
NLS stands for National Language Support. This is an architecture, and it helps us to perform actions on data in our native languages, for example, processing or retrieving data.
Thanks to the use of NLS, we can not worry about the fact that the database will incorrectly display something in our data. For example, the date will be in the wrong format, or the currency units will indicate another country’s currency units.
NLS architecture adjusts everything to our native language and our location, whether it is the sorting order, date format, currency, or error messages.
What is NLS_DATE_FORMAT
NLS_DATE_FORMAT is a string parameter that is responsible for the default date format, which we are able to use with the TO_CHAR and TO_DATE functions.
However, the default value of this function goes from the NLS_TERRITORY parameter.
This function, in turn, is responsible for the rules of days and weeks numbering in the database since it indicates the name of the territory. This function is also responsible for a number of other standard values.
The syntax of the NLS_DATE_FORMAT parameter is:
NLS_DATE_FORMAT = "MM/DD/YYYY"
As we see, the value of this parameter can be any form of DATE format, and just the value must be specified in quotation marks.
Oracle examples on how to set NLS_DATE_FORMAT
Let’s first use the command that changes the session parameter.
This command looks like:
ALTER SESSION SET NLS_DATE_FORMAT = <new_format>
Now we will check the value that is set in the database using a special view, namely the sys.v_$nls_parameters.
sys.v_$nls_parameters is a view where the data like date and time are stored.
Our query is:
SELECT * FROM sys.v_$nls_parameters WHERE parameter='NLS_DATE_FORMAT'
This is the result we might receive:
Figure 1. The result of the NLS_DATE_FORMAT
Now we will set the NLS_DATE_FORMAT for the user session by using the ALTER SESSION command. Let’s perform the following script:
SELECT SYSDATE FROM DUAL; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD'; SELECT SYSDATE FROM DUAL;
The result is:
Figure 2. NLS_DATE_FORMAT for the user session
First, the script returns the current date in the format that is set on the database.
After that, the ALTER SESSION command changes the parameter for the current session.
Next, we re-get the current date in a new format.
As we can see, we used the DUAL table in our query. The DUAL table is a system table.
Sometimes we may need to get the result of some stored function or the result of a calculation using a query. For this, this DUAL system table is useful. This table is always accessible to all users, and it always contains one column with the name DUMMY with VARCHAR2 (1) type and one row.