Introduction
Configuring NLS_TERRITORY in an Oracle APEX application ensures that dates, numbers, and currencies are formatted according to the user's regional standards. This setting defines territory-specific behaviors such as decimal separators, date formats, and first day of the week. Applying the appropriate NLS_TERRITORY improves clarity, user comfort, and localization accuracy in multi-regional applications.
Using NLS_TERRITORY in Oracle APEX allows you to control how dates, numbers, currency symbols, and the first day of the week are displayed throughout your application, based on the user’s regional settings. This setting helps localize content for a specific territory, improving clarity and user experience in multi-language or international environments.
Here is how to use NLS_TERRITORY in detail within an Oracle APEX application:
-
Understand the Role of NLS_TERRITORY
NLS_TERRITORYdetermines:-
Default date format
-
Decimal and group separators
-
Currency symbol and its placement
-
The first day of the week
Each territory (e.g.,US,FR,JP) comes with its own formatting conventions. For example: -
US→ mm/dd/yyyy, dot as decimal separator -
FR→ dd/mm/yyyy, comma as decimal separator
-
-
Determine Where to Set It
In Oracle APEX, you can setNLS_TERRITORYat the session level, giving you control for each user. This is typically done using a PL/SQL process at the application or page level. -
Create a Dynamic Session-Level Process
In your application, go to Shared Components > Application Processes (or use a Before Header page process), and create a process like this:BEGIN IF :APP_LANGUAGE = 'fr' THEN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''FR'''; ELSIF :APP_LANGUAGE = 'de' THEN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''GERMANY'''; ELSE EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'''; END IF; END;This logic adjusts the territory based on the value of an application item like
:APP_LANGUAGEor a user preference stored in a custom table. -
Allow Users to Choose Territory (Optional)
To provide more flexibility:-
Create an application item (e.g.,
G_TERRITORY) or a user preference table. -
Populate a dropdown list of supported territories.
-
Use this value in your initialization PL/SQL to set
NLS_TERRITORY.
-
-
Test Format Behavior in the UI
After settingNLS_TERRITORY, test how your application behaves:-
Are date pickers displaying the correct format?
-
Are numbers shown with the correct separators?
-
Is currency formatting consistent?
These will adjust automatically based on the session-level territory setting.
-
-
Avoid Overriding Territory With Format Masks
If you use explicit format masks in item properties (likeDD-MON-YYYY), they overrideNLS_TERRITORY. To benefit from localization, prefer using generic or dynamic format settings unless strict formatting is required. -
Check Current Settings (Optional Debug)
Use the following query to view current session NLS settings:SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TERRITORY';This helps verify that the
ALTER SESSIONcommand is working as expected.
By using NLS_TERRITORY correctly in Oracle APEX, you can align the application experience with users’ regional expectations. It enhances clarity, reduces confusion with formats, and supports international usability, making your application both professional and globally accessible.
There are multiple ways to configure NLS_TERRITORY within Oracle APEX:
1. Setting NLS_TERRITORY at the Database Level
If you want the NLS_TERRITORY setting to apply globally across the entire database, you can modify it using the ALTER SESSION command.
ALTER SESSION SET NLS_TERRITORY = 'GERMANY';
This will update the session-specific settings for numeric formatting, date formats, and currency symbols according to German standards.
To check the current NLS_TERRITORY setting in the session, run:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TERRITORY';
2. Setting NLS_TERRITORY at the Application Level
Within Oracle APEX, you can define NLS_TERRITORY settings at the Application Level.
Open App Builder and select your application.
Navigate to Shared Components.
Click Globalization Attributes.
In the Date Format section, set the desired NLS_TERRITORY.
Click Apply Changes.
This ensures that the selected country format applies throughout the application.
Dynamically Changing NLS_TERRITORY
3. Setting NLS_TERRITORY Per User
If you want different users to have different NLS_TERRITORY settings based on their location, you can modify it dynamically during login.
Create a Before Header Process on the Login Page to set the NLS_TERRITORY dynamically:
BEGIN
IF APEX_UTIL.GET_SESSION_LANG = 'fr' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''FRANCE''';
ELSIF APEX_UTIL.GET_SESSION_LANG = 'de' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''GERMANY''';
ELSE
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''UNITED STATES''';
END IF;
END;
This ensures that users get the correct regional settings based on their language preference.
Using NLS_TERRITORY in SQL Queries
Once the NLS_TERRITORY setting is applied, it automatically affects how dates, numbers, and currency values are formatted in SQL queries.
Example 1: Formatting Dates Based on NLS_TERRITORY
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
If NLS_TERRITORY is set to "GERMANY", this will return dates in the DD.MM.YYYY format instead of the US MM/DD/YYYY format.
Example 2: Formatting Numbers Based on NLS_TERRITORY
SELECT TO_CHAR(1234567.89, '999G999G999D99') FROM DUAL;
In "UNITED STATES", the output will be 1,234,567.89.
In "FRANCE", it will be 1 234 567,89.
In "GERMANY", it will be 1.234.567,89.
Example 3: Formatting Currency Based on NLS_TERRITORY
SELECT TO_CHAR(1000, 'L999G999D99') FROM DUAL;
In "UNITED STATES", the output will be $1,000.00.
In "UNITED KINGDOM", it will be £1,000.00.
In "JAPAN", it will be ¥1,000.
Applying NLS_TERRITORY in PL/SQL
If you need to apply NLS_TERRITORY settings inside a PL/SQL block, you can use:
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''ITALY''';
END;
To get the current NLS_TERRITORY value inside a PL/SQL block:
DECLARE
v_territory VARCHAR2(50);
BEGIN
SELECT VALUE INTO v_territory
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_TERRITORY';
DBMS_OUTPUT.PUT_LINE('Current Territory: ' || v_territory);
END;
Best Practices for Using NLS_TERRITORY
Set NLS_TERRITORY early in a session to avoid inconsistencies in date or number formatting.
Use session-based settings instead of global database settings to allow different users to have different formats.
Test different locales by changing the NLS_TERRITORY value to ensure proper formatting.
Store user preferences in a database table and apply them dynamically when a user logs in.
Example:
DECLARE
v_user_territory VARCHAR2(50);
BEGIN
SELECT user_territory INTO v_user_territory FROM user_preferences WHERE user_id = :APP_USER;
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''' || v_user_territory || '''';
END;
This way, users can choose their preferred settings, and the application will automatically adjust.
Implementing NLS_TERRITORY in Oracle APEX ensures that numeric, currency, and date formats are properly displayed according to a user’s regional preferences. By setting NLS_TERRITORY at the database, application, or session level, and using it in SQL and PL/SQL queries, you can build a globally adaptable application.
This enhances the user experience by allowing automatic formatting of data based on local conventions, ensuring better readability and usability for international users.
NLS_LANGUAGE and NLS_TERRITORY determine the default presentation of numbers, dates, and currencies.
Conclusion
By configuring NLS_TERRITORY at the session level within your APEX application, you align data presentation with regional expectations. Whether based on user preferences, browser language, or application logic, setting this parameter correctly provides users with a more intuitive and culturally consistent experience across your app.
No comments:
Post a Comment